[GENERAL] provide pg_get_notifications()
Hi, Please provide a pg_get_notifications() function (or similar) as a complement to pg_notify(). Currently, in java jdbc, the clients must poll for notifications using getNotifications() method in org.postgresql.PGConnection. So, clients must obtain a reference to connection object and cast it down (presumably from java.sql.Connection) to org.postgresql.PGConnection. It is very problematic to not be able to use the connection as java.sql.Connection. In a typical set-up, the connection must be obtained from a ORM framework (like hibernate) which wraps the underlying connection in its own dynamic proxy. The underling connection to ORM itself may be wrapped up in another proxy by a connection pool like commons.apache.org/pool . And if you want to be fancy, the underlying connection to commons-pool may be wrapped in another proxy by connection profilers like log4jdbc/jdbmonitor/javamelody. If the programmer knows the set-up, he can of course overcome the problem by using framework-specific knowledge to obtain a PGConnection (using a lot of downcasts) or reflection tricks (not desirable). However, in some cases, the programmer cannot possibly know the set-up in advance. For example if he writes a middleware/help library. And thus the need for pg_get_notifications() function. thanks, -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] custom system catalog table names
Hi, How can one create a postgres build with custom system table names? For example, instead of pg_class, I'd like to name the table as foobar_class. Or instead of pg_catalog, foobar_catalog. I don't care about compatiblity with pgadmin, jdbc drivers, etc. I'd be happy just as long as I have a functional psql client. thanks -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] writing a plpgsql query for the first time
Hi, I am having trouble writing my first plpgsql query. Any idea why the following plpgsql does not work?? test=# drop function if exists testfunc() ; DROP FUNCTION test=# create function testfunc() test-# returns table (id int, code char(1)) as $$ test$# BEGIN test$# return query select id, code from record_table where id 2; test$# END; test$# $$ language plpgsql; CREATE FUNCTION test=# test=# select * from testfunc(); id | code +-- (0 rows) test=# select * from record_table; id | code +-- 1 | 1 3 | 3 (2 rows) If I take out id 2 from the function, in the where clause, it returns: test=# select * from testfunc(); NOTICE: hi id | code +-- | | (2 rows) 2 rows...but no actual data?? Here is the table definition: test=# \d record_table; Table public.record_table Column | Type | Modifiers +--+- id | integer | not null default nextval('record_id_seq'::regclass) code | character(1) | Indexes: record_pkey PRIMARY KEY, btree (id) Any idea what's happening? thanks -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] heap-only tuples, and constraints
Hi, Question about heap-only tuples, and constraint checking. Does this statement (UPDATE user SET email='newChangedVal', password='existingVal') requires updating an index on user.password? Or more generally, if an UPDATE includes an explicit but unchanged value for an index column, does postgres need to also update the index entries? Or does HOT apply only when indexed column is missing or not explicit (i.e. password=password)? Along the same line of thought as above, if password is a foreign key column and if an UPDATE includes an explicit but unchanged value for this fk column, does postgres need to check constraint satisfiability? Thanks -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general