Re: Storing PHP 5.3 sessions into PostgreSQL 8.4

[Adam Cornett <adam.cornett@xxxxxxxxx>]

On Wed, Sep 21, 2011 at 10:53 AM, Alexander Farber <alexander.farber@xxxxxxxxx> wrote:

Hello,

I'm using CentOS 6.0 Linux 64 bit with the stock packages:

# rpm -qa|grep php
php-cli-5.3.2-6.el6_0.1.x86_64
php-5.3.2-6.el6_0.1.x86_64
php-xml-5.3.2-6.el6_0.1.x86_64
php-pgsql-5.3.2-6.el6_0.1.x86_64
php-pear-1.9.0-2.el6.noarch
php-pdo-5.3.2-6.el6_0.1.x86_64
php-common-5.3.2-6.el6_0.1.x86_64

#  rpm -qa|grep postgres
postgresql-devel-8.4.7-1.el6_0.1.x86_64
postgresql-docs-8.4.7-1.el6_0.1.x86_64
postgresql-libs-8.4.7-1.el6_0.1.x86_64
postgresql-8.4.7-1.el6_0.1.x86_64
postgresql-server-8.4.7-1.el6_0.1.x86_64

and would like to change my own PHP script from using
$_SERVER['REMOTE_USER'] to using $_SESSION,
but don't have any experience with PHP sessions yet.

I'd like the (quite extensive) user data to be stored into
the PostgreSQL and only save a "user id" in $_SESSION.

However the web page
http://www.php.net/manual/en/session-pgsql.installation.php
says "This extension is considered unmaintained and dead".

Does anybody please have any advice what to do here?

Maybe I can save session data into the db myself (and how)?

Thank you
Alex

--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

I have attached some SQL and PHP that we use to store sessions in the database, its based off http://www.php.net/manual/en/function.session-set-save-handler.php

I also in our nightly maintenance script I delete sessions older than 24 hours as some times php doesn't GC sessions right under CGI.

delete from sessions where ts < (now() - '24 hours'::interval);

--
Adam Cornett

-- Database: session_db

-- DROP DATABASE session_db;

CREATE DATABASE session_db
  WITH OWNER = "session_user"
       ENCODING = 'UTF8'
       TABLESPACE = pg_default
       LC_COLLATE = 'en_US.UTF-8'
       LC_CTYPE = 'en_US.UTF-8'
       CONNECTION LIMIT = -1;



-- Table: sessions

-- DROP TABLE sessions;

CREATE TABLE sessions
(
  session_id character varying(200) NOT NULL,
  ts timestamp with time zone DEFAULT now(),
  session_data text,
  CONSTRAINT sessions_pkey PRIMARY KEY (session_id )
)
WITH (
  OIDS=FALSE
);
ALTER TABLE sessions
  OWNER TO "session_user";

-- Index: session_id_index

-- DROP INDEX session_id_index;

CREATE INDEX session_id_index
  ON sessions
  USING btree
  (session_id COLLATE pg_catalog."default" );


-- Function: upsert_session(character varying, text)

-- DROP FUNCTION upsert_session(character varying, text);

CREATE OR REPLACE FUNCTION upsert_session(id character varying, data text)
  RETURNS void AS
$BODY$
BEGIN
    LOOP
        -- first try to update the session
        UPDATE sessions SET session_data = data WHERE session_id = id;
        IF found THEN
            RETURN;
        END IF;
        -- not there, so try to insert the session
        -- if someone else inserts the same session concurrently,
        -- we could get a unique-key failure
        BEGIN
            INSERT INTO sessions(session_id,session_data) VALUES (id, data);
            RETURN;
        EXCEPTION WHEN unique_violation THEN
            -- do nothing, and loop to try the UPDATE again
        END;
    END LOOP;
END;

$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
ALTER FUNCTION upsert_session(character varying, text)
  OWNER TO "session_user";

Attachment: dbsession.inc.php
Description: application/httpd-php

-- 
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general