[SQL] Transaction-specific global variable
hstore greatly simplifies creating triggers for logging table changes, which is great. However, when creating a log record, I would like to include information about the party who made this change. We generally do not allow direct database access for application code, so the PostgreSQL user does not provide sufficient information on its own. Instead, I'd like to create a transaction-specific variable which stores context information to be included in the log table. I suppose I could create a stored procedures in C which provides this functionality, but I wonder if there is already something similar I could reuse. For instance, I could reuse the application_name configuration variable, but this seems a bit gross. (If you could recommend logging functionality I could learn from, that would be welcome, too.) -- Florian Weimer BFK edv-consulting GmbH http://www.bfk.de/ Kriegsstraße 100 tel: +49-721-96201-1 D-76133 Karlsruhe fax: +49-721-96201-99 -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] UNIQUE on everything except primary key
I have a table with 11 columns and want to eliminate duplication on INSERTs. What's the best method? The obvious solution would be to create a UNIQUE index on everything except the primary key - 'id' - but that's a 10-column index and doesn't sound very efficient. Am I missing something more obvious? gvim -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] UNIQUE on everything except primary key
Did you check check constraints? http://www.postgresql.org/docs/8.4/static/ddl-constraints.html Best Andreas -Ursprüngliche Nachricht- Von: pgsql-sql-ow...@postgresql.org [mailto:pgsql-sql-ow...@postgresql.org] Im Auftrag von gvim Gesendet: Donnerstag, 3. Februar 2011 16:44 An: pgsql sql Betreff: [SQL] UNIQUE on everything except primary key I have a table with 11 columns and want to eliminate duplication on INSERTs. What's the best method? The obvious solution would be to create a UNIQUE index on everything except the primary key - 'id' - but that's a 10-column index and doesn't sound very efficient. Am I missing something more obvious? gvim -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Transaction-specific global variable
Florian Weimer writes: > hstore greatly simplifies creating triggers for logging table changes, > which is great. However, when creating a log record, I would like to > include information about the party who made this change. We > generally do not allow direct database access for application code, so > the PostgreSQL user does not provide sufficient information on its > own. Instead, I'd like to create a transaction-specific variable > which stores context information to be included in the log table. I > suppose I could create a stored procedures in C which provides this > functionality, but I wonder if there is already something similar I > could reuse. For instance, I could reuse the application_name > configuration variable, but this seems a bit gross. You could abuse the custom-GUC-variable mechanism: just set custom_variable_classes and then define some variable that doesn't actually have any underlying loadable module. regards, tom lane -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] using of select (myfunction()).* is so slow
Hi all, im using a function of my own in a subquery, and when wonderig about the slowliness of this one, y relalize that: test=# SELECT (_xxfunction(854,'711H',11,false)).* ; (4 filas) --Result DELETED Duración: 1069,465 ms glyms=# SELECT * from _xxfunction(854,'711H',11,false) ; (4 filas) Duración: 228,699 ms For privacy reasons, i just deleted the result and the function name, but the resulst are obviously exactly the same, and the ().* form (which i needed) is taking so much more...there is a reason why? A workaround? Thanks! Gerardo -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] using of select (myfunction()).* is so slow
Hello If you use a record expansion over function's result, then function is called once for record's field. so don't do it on slow functions. Regards Pavel 2011/2/3 Gerardo Herzig : > Hi all, im using a function of my own in a subquery, and when wonderig > about the slowliness of this one, y relalize that: > > test=# SELECT (_xxfunction(854,'711H',11,false)).* ; > (4 filas) > --Result DELETED > Duración: 1069,465 ms > > > glyms=# SELECT * from _xxfunction(854,'711H',11,false) ; > (4 filas) > Duración: 228,699 ms > > For privacy reasons, i just deleted the result and the function name, > but the resulst are obviously exactly the same, and the ().* form (which > i needed) is taking so much more...there is a reason why? A workaround? > > Thanks! > > Gerardo > > > -- > Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-sql > -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] UNIQUE on everything except primary key
Do all the 10 columns make a natural key? 03.02.2011, 18:44, "gvim" : > I have a table with 11 columns and want to eliminate duplication on INSERTs. > What's the best method? The obvious solution would be to create a UNIQUE > index on everything except the primary key - 'id' - but that's a 10-column > index and doesn't sound very efficient. Am I missing something more obvious? > > gvim > > -- > Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-sql -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] How to checking the existance of constraints for a table?
Hello, I have a sample table describe as follows, anyone knows how to checking the existence of a constraint? oviddb=# \d myTable Column | Type | Modifiers -+--+--- orderid | smallint | not null modelid | smallint | not null Indexes: "mytable_orderid_key" UNIQUE, btree (orderid) For example, how to know that myTable has a constraint with name "mytable_orderid_key"? I have this problem because when doing migration script, some old table may have this constraint or may not have. And after migration, we want to add this constraint: If I just run the command: alter table myTable add Unique (orderid) in the migration script, when the constraint exits, a new index will be created as "mytable_orderid_key1" UNIQUE, btree (orderid) So, it causes some duplication. oviddb=# \d myTable Column | Type | Modifiers -+--+--- orderid | smallint | not null modelid | smallint | not null Indexes: "mytable_orderid_key" UNIQUE, btree (orderid) "mytable_orderid_key1" UNIQUE, btree (orderid) My question becomes how to write sql statement with following semantecs if constraint unique (orderid) exists do nothing else alter table myTable add Unique (orderid) Thank you so much! -- View this message in context: http://postgresql.1045698.n5.nabble.com/How-to-checking-the-existance-of-constraints-for-a-table-tp3368417p3368417.html Sent from the PostgreSQL - sql mailing list archive at Nabble.com. -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] How to workaround DROP CONSTRAINT [ IF EXISTS ] in Postgres version 8.1?
Hello, I found that DROP CONSTRAINT [ IF EXISTS ] is available in Postgres 9.1 http://developer.postgresql.org/pgdocs/postgres/sql-altertable.html http://developer.postgresql.org/pgdocs/postgres/sql-altertable.html But it is not avaiable in Postgres 8.1 http://www.postgresql.org/docs/8.1/interactive/sql-altertable.html http://www.postgresql.org/docs/8.1/interactive/sql-altertable.html Is there a way to achieve this on Postgres 8.1? Thanks a lot -- View this message in context: http://postgresql.1045698.n5.nabble.com/How-to-workaround-DROP-CONSTRAINT-IF-EXISTS-in-Postgres-version-8-1-tp3368570p3368570.html Sent from the PostgreSQL - sql mailing list archive at Nabble.com. -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] out of shared memory - find temporary tables
Hi there, I got "out of shared memory" error. Searching on postgresql forums, I found this it occurs probably because of intensive use of temporary tables in one transaction. I'm locking in pg_locks table, and I found some rows with the following modes: "ShareLock", "AccessExclusiveLock", "ExclusiveLock", "AccessShareLock", and "RowExclusiveLock" with many counts (especially "AccessExclusiveLock" and "AccessShareLock"), but the oid and relname is empty. I suppose there are related to some temporary tables. How can I find what are the storage procedures which create these temporary tables in my code ? TIA, Sabin -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] how control update rows
Hi there, I'd like to control the rows which are updated. I found useful the option RETURNING in UPDATE syntaxt. Can I process the rows wich are returning there ? I think to something like that: SELECT * FROM ( UPDATE "T" SET "C" = 1 WHERE "ID" > 100 RETURNING * ) x TIA, Sabin -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] how control update rows
Hello 2011/2/3 Sabin Coanda : > Hi there, > > I'd like to control the rows which are updated. I found useful the option > RETURNING in UPDATE syntaxt. Can I process the rows wich are returning there > ? > > I think to something like that: > > SELECT * > FROM ( > UPDATE "T" SET > "C" = 1 > WHERE "ID" > 100 > RETURNING * > ) x > It's not implemented yet. You can use a stored procedure or temp tables instead. Regards Pavel Stehule > TIA, > Sabin > > > > -- > Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-sql > -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
