Re: [GENERAL] Savepoint and prepared transactions
On Tue, May 4, 2010 at 11:52 PM, Konstantin Izmailov pgf...@gmail.com wrote: Hi, I'm working on a solution that utilizes 2 phase commit protocol (between SQL Server and PostgreSQL). Normally PostgreSQL statements sequense is: 1. START 2. inserts, updates, etc. 3. PREPARE TRANSACTION 'uuid' 4. COMMIT PREPARED 'uuid' What if on step 2 user application issues statements with SAVEPOINTs, e.g. 2.1. SAVEPOINT svp1 2.2. inserts, updates, etc. 2.3. SAVEPOINT svp2 2.4. inserts, updates, etc. 2.5. RELEASE SAVEPOINT svp2 2.6. ROLLBACK TO SAVEPOINT svp1 2.7. inserts, updates, etc. Is this allowed and safe to use? What are you expecting to happen? -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] no such file euc2004... while initdb 9.0Beta
Hello, I've tried to install the latest 9.0Beta (Fedora12, pgdg repository), but with no success. initdb returns with an error described below. blub# yum list postgres* Loaded plugins: refresh-packagekit Installed Packages postgresql.i3869.0-alpha4_1PGDG.fc12 @pgdg90 postgresql-docs.i386 9.0-alpha4_1PGDG.fc12 @pgdg90 postgresql-libs.i386 9.0-alpha4_1PGDG.fc12 @pgdg90 postgresql-server.i3869.0-alpha4_1PGDG.fc12 @pgdg90 blub# /etc/init.d/postgresql-9.0 initdb Initializing database: [FAILED] blub# cat /var/lib/pgsql/9.0/pgstartup.log The files belonging to this database system will be owned by user postgres. This user must also own the server process. The database cluster will be initialized with locale en_US.UTF-8. The default database encoding has accordingly been set to UTF8. The default text search configuration will be set to english. fixing permissions on existing directory /var/lib/pgsql/9.0/data ... ok creating subdirectories ... ok selecting default max_connections ... 100 selecting default shared_buffers ... 28MB creating configuration files ... ok creating template1 database in /var/lib/pgsql/9.0/data/base/1 ... ok initializing pg_authid ... ok initializing dependencies ... ok creating system views ... ok loading system objects' descriptions ... ok creating conversions ... FATAL: could not access file $libdir/euc2004_sjis2004: No such file or directory STATEMENT: CREATE OR REPLACE FUNCTION euc_jis_2004_to_shift_jis_2004 (INTEGER, INTEGER, CSTRING, INTERNAL, INTEGER) RETURNS VOID AS '$libdir/euc2004_sjis2004', 'euc_jis_2004_to_shift_jis_2004' LANGUAGE C STRICT; child process exited with exit code 1 initdb: removing contents of data directory /var/lib/pgsql/9.0/data == Where should the file euc2004... with this function euc... come from ..? thanks in advanceGERD. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] no such file euc2004... while initdb 9.0Beta
On Wed, May 5, 2010 at 4:05 PM, Gerd Koenig koe...@transporeon.com wrote: Hello, I've tried to install the latest 9.0Beta (Fedora12, pgdg repository), but with no success. initdb returns with an error described below. blub# yum list postgres* Loaded plugins: refresh-packagekit Installed Packages postgresql.i3869.0-alpha4_1PGDG.fc12 @pgdg90 postgresql-docs.i386 9.0-alpha4_1PGDG.fc12 @pgdg90 postgresql-libs.i386 9.0-alpha4_1PGDG.fc12 @pgdg90 postgresql-server.i3869.0-alpha4_1PGDG.fc12 @pgdg90 Looks like you still using the alpha4 packages, try following this: http://people.planetpostgresql.org/devrim/index.php?/archives/43-How-to-install-PostgreSQL-9.0-Beta-1-to-FedoraCentOSRHEL.html http://people.planetpostgresql.org/devrim/index.php?/archives/43-How-to-install-PostgreSQL-9.0-Beta-1-to-FedoraCentOSRHEL.htmland see if that fixes the problem. -- Shoaib Mir http://shoaibmir.wordpress.com/
Re: [GENERAL] no such file euc2004... while initdb 9.0Beta
Hi Shoaib, thanks for your hint it solved the problem just by yum update postgresql-* since I had the correct repository already ;-) regards...GERD... On Wednesday 05 May 2010 08:28:08 am Shoaib Mir wrote: On Wed, May 5, 2010 at 4:05 PM, Gerd Koenig koe...@transporeon.com wrote: Hello, I've tried to install the latest 9.0Beta (Fedora12, pgdg repository), but with no success. initdb returns with an error described below. blub# yum list postgres* Loaded plugins: refresh-packagekit Installed Packages postgresql.i3869.0-alpha4_1PGDG.fc12 @pgdg90 postgresql-docs.i386 9.0-alpha4_1PGDG.fc12 @pgdg90 postgresql-libs.i386 9.0-alpha4_1PGDG.fc12 @pgdg90 postgresql-server.i3869.0-alpha4_1PGDG.fc12 @pgdg90 Looks like you still using the alpha4 packages, try following this: http://people.planetpostgresql.org/devrim/index.php?/archives/43-How-to-ins tall-PostgreSQL-9.0-Beta-1-to-FedoraCentOSRHEL.html http://people.planetpostgresql.org/devrim/index.php?/archives/43-How-to-in stall-PostgreSQL-9.0-Beta-1-to-FedoraCentOSRHEL.htmland see if that fixes the problem. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] no such file euc2004... while initdb 9.0Beta
On Wed, 2010-05-05 at 08:05 +0200, Gerd Koenig wrote: $libdir/euc2004_sjis2004: No such file or directory STATEMENT: CREATE OR REPLACE FUNCTION euc_jis_2004_to_shift_jis_2004 (INTEGER, INTEGER, CSTRING, INTERNAL, INTEGER) RETURNS VOID AS '$libdir/euc2004_sjis2004', 'euc_jis_2004_to_shift_jis_2004' LANGUAGE C STRICT; child process exited with exit code 1 initdb: removing contents of data directory /var/lib/pgsql/9.0/data == Where should the file euc2004... with this function euc... come from ..? This is an known issue with alpha4 RPMs, and I fixed it in 2nd set: http://svn.pgrpms.org/browser/rpm/redhat/9.0/postgresql/F-12/postgresql-9.0.spec#L758 A workaround is installing contrib package. Please install beta 1 RPMs. They are available in the same repository. A mini howto is here: http://people.planetpostgresql.org/devrim/index.php?/archives/43-How-to-install-PostgreSQL-9.0-Beta-1-to-FedoraCentOSRHEL.html Regards, -- Devrim GÜNDÜZ PostgreSQL Danışmanı/Consultant, Red Hat Certified Engineer PostgreSQL RPM Repository: http://yum.pgrpms.org Community: devrim~PostgreSQL.org, devrim.gunduz~linux.org.tr http://www.gunduz.org Twitter: http://twitter.com/devrimgunduz signature.asc Description: This is a digitally signed message part
Re: [GENERAL] Savepoint and prepared transactions
I expect that only changes on step 2.7 persisted in DB. On Tue, May 4, 2010 at 11:18 PM, Scott Marlowe scott.marl...@gmail.comwrote: On Tue, May 4, 2010 at 11:52 PM, Konstantin Izmailov pgf...@gmail.com wrote: Hi, I'm working on a solution that utilizes 2 phase commit protocol (between SQL Server and PostgreSQL). Normally PostgreSQL statements sequense is: 1. START 2. inserts, updates, etc. 3. PREPARE TRANSACTION 'uuid' 4. COMMIT PREPARED 'uuid' What if on step 2 user application issues statements with SAVEPOINTs, e.g. 2.1. SAVEPOINT svp1 2.2. inserts, updates, etc. 2.3. SAVEPOINT svp2 2.4. inserts, updates, etc. 2.5. RELEASE SAVEPOINT svp2 2.6. ROLLBACK TO SAVEPOINT svp1 2.7. inserts, updates, etc. Is this allowed and safe to use? What are you expecting to happen?
Re: [GENERAL] (psuedo) random serial for PK or at least UNIQUE NOT NULL?
2010/5/4 Alvaro Herrera alvhe...@commandprompt.com: Vincenzo Romano wrote: Hi all. I'm willing to change an BIGINT ID column (actually a SERIAL8) with a BIGINT whose valules are (pseudo)random. The main objective is to avoid guessability. I whish I could also use it as the PK (as it's now) but that's not really important now. Any hint? http://wiki.postgresql.org/wiki/Pseudo_encrypt That's a nice starting point. I should apply that function twice for each 32-bit chunk of a 64-bit BIGINT and then merge the results into a single BIGINT ... Boring but doable! -- Vincenzo Romano NotOrAnd Information Technologies NON QVIETIS MARIBVS NAVTA PERITVS -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] temp sequence
I am using 8.2.14 I am trying to use a temp sequence in a function and I'm having a lot of trouble. I create the temp sequence and then I have to drop it at the end of the function, because it stays alive for the whole session and not just the function. I want to use the nextval function in an update statement so it gives a sequence number to each row it updates. If I drop the sequence and recreate it I get an OID not found error. I tried to run the update statement dynamically, but part of the update stmt is an int array variable and it won't cast it to text. My goal in the end is that every row that is updated will be numbered sequentially per update. Do you have any ideas? Sim -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] database Benchmark, TPC, PostgreSQL and TPC-E
Looking for database benchmarks I fell into the Transaction Processing Performance Council Some questions on it. are there somewhere some results of TPC-* benchmarks for PostgreSQL? Are there reliable database tests? There are open source implementation? There are other benchmarks that would be worth to consider to use, or implement? Looking on the TPC-E 1.10.0 specification history I saw that Enterprise DB has quitted the membership list (14 march 2008) What does it mean for PostgreSQL? thank you Davide -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Function not RAISE NOTICE if a parameter is NULL
Oh... Thanks. What a lame I'm. Best Regards, On Wed, May 5, 2010 at 1:25 AM, Tom Lane t...@sss.pgh.pa.us wrote: Andre Lopes lopes80an...@gmail.com writes: Thanks for the reply. I have not call it STRICT... Yes you are: RETURNS NULL ON NULL INPUT regards, tom lane
[GENERAL] alter table alter type CASCADE
One of the biggest problems I have maintaining a database with a lot of views is that when I want to change a datatype, I have to drop every view uses the column and every view that uses those views etc... This turns into a maintenance nightmare. Is there any intention of adding a CASCADE to alter type which would automatically update any dependencies with the new datatype? Obviously it should error out if it wouldn't have let you save one of the views with the new datatype. Thanks Sim -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] alter table alter type CASCADE
2010/5/5 Sim Zacks s...@compulab.co.il One of the biggest problems I have maintaining a database with a lot of views is that when I want to change a datatype, I have to drop every view uses the column and every view that uses those views etc... This turns into a maintenance nightmare. Is there any intention of adding a CASCADE to alter type which would automatically update any dependencies with the new datatype? Obviously it should error out if it wouldn't have let you save one of the views with the new datatype. Thanks Sim -- Yes, there is an intention of adding such functionality (3rd item) but probably not very straightforward: http://wiki.postgresql.org/wiki/Todo#Views_.2F_Rules Regards Thom
Re: [GENERAL] (psuedo) random serial for PK or at least UNIQUE NOT NULL?
2010/5/5 Vincenzo Romano vincenzo.rom...@notorand.it: 2010/5/4 Alvaro Herrera alvhe...@commandprompt.com: Vincenzo Romano wrote: Hi all. I'm willing to change an BIGINT ID column (actually a SERIAL8) with a BIGINT whose valules are (pseudo)random. The main objective is to avoid guessability. I whish I could also use it as the PK (as it's now) but that's not really important now. Any hint? http://wiki.postgresql.org/wiki/Pseudo_encrypt That's a nice starting point. I should apply that function twice for each 32-bit chunk of a 64-bit BIGINT and then merge the results into a single BIGINT ... Boring but doable! Hum, not sure. If I understand correctly, you won't be able to get the original int64 from the generated one. Better rewrite the function for 2^64 isn't it ? -- Vincenzo Romano NotOrAnd Information Technologies NON QVIETIS MARIBVS NAVTA PERITVS -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- Cédric Villemain -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] (psuedo) random serial for PK or at least UNIQUE NOT NULL?
http://wiki.postgresql.org/wiki/Pseudo_encrypt That's a nice starting point. I should apply that function twice for each 32-bit chunk of a 64-bit BIGINT and then merge the results into a single BIGINT ... Boring but doable! Hum, not sure. If I understand correctly, you won't be able to get the original int64 from the generated one. Why should I bother? Better rewrite the function for 2^64 isn't it ? Sure!. -- Vincenzo Romano NotOrAnd Information Technologies NON QVIETIS MARIBVS NAVTA PERITVS -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] (psuedo) random serial for PK or at least UNIQUE NOT NULL?
2010/5/5 Vincenzo Romano vincenzo.rom...@notorand.it: http://wiki.postgresql.org/wiki/Pseudo_encrypt That's a nice starting point. I should apply that function twice for each 32-bit chunk of a 64-bit BIGINT and then merge the results into a single BIGINT ... Boring but doable! Hum, not sure. If I understand correctly, you won't be able to get the original int64 from the generated one. Why should I bother? Dunno, but it was in the original spec. Better rewrite the function for 2^64 isn't it ? Sure!. -- Vincenzo Romano NotOrAnd Information Technologies NON QVIETIS MARIBVS NAVTA PERITVS -- Cédric Villemain -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] alter table alter type CASCADE
It has been some years since I worked with Oracle, doesn't Oracle recompile the view when the object it references changes in structure? Send From: thombr...@gmail.com Date: Wed, 5 May 2010 10:12:34 +0100 Subject: Re: [GENERAL] alter table alter type CASCADE To: s...@compulab.co.il CC: pgsql-general@postgresql.org 2010/5/5 Sim Zacks s...@compulab.co.il One of the biggest problems I have maintaining a database with a lot of views is that when I want to change a datatype, I have to drop every view uses the column and every view that uses those views etc... This turns into a maintenance nightmare. Is there any intention of adding a CASCADE to alter type which would automatically update any dependencies with the new datatype? Obviously it should error out if it wouldn't have let you save one of the views with the new datatype. Thanks Sim -- Yes, there is an intention of adding such functionality (3rd item) but probably not very straightforward: http://wiki.postgresql.org/wiki/Todo#Views_.2F_Rules Regards Thom _ Win a $10,000 shopping spree from Hotmail! Enter now. http://go.microsoft.com/?linkid=9729711
Re: [GENERAL] alter table alter type CASCADE
On 5 May 2010 13:14, Chris Barnes compuguruchrisbar...@hotmail.com wrote: It has been some years since I worked with Oracle, doesn't Oracle recompile the view when the object it references changes in structure? Send What does Oracle do when you've got a view like: CREATE OR REPLACE VIEW test_view AS SELECT test.test_id, test.test_value, date_is_future(test.test_date) as upcoming FROM test; Where date_is_future expects a text field, but you change the test table so that test_date is now varchar(20)? The function no longer matches the signature. Does it just prevent it? And what about when conditions are provided in a WHERE clause which become invalid when the column type changes? Thom
Re: [GENERAL] Savepoint and prepared transactions
Should work. I'm not sure 2.5 release savepoint is necessary. On Wed, May 5, 2010 at 12:53 AM, Konstantin Izmailov pgf...@gmail.com wrote: I expect that only changes on step 2.7 persisted in DB. On Tue, May 4, 2010 at 11:18 PM, Scott Marlowe scott.marl...@gmail.com wrote: On Tue, May 4, 2010 at 11:52 PM, Konstantin Izmailov pgf...@gmail.com wrote: Hi, I'm working on a solution that utilizes 2 phase commit protocol (between SQL Server and PostgreSQL). Normally PostgreSQL statements sequense is: 1. START 2. inserts, updates, etc. 3. PREPARE TRANSACTION 'uuid' 4. COMMIT PREPARED 'uuid' What if on step 2 user application issues statements with SAVEPOINTs, e.g. 2.1. SAVEPOINT svp1 2.2. inserts, updates, etc. 2.3. SAVEPOINT svp2 2.4. inserts, updates, etc. 2.5. RELEASE SAVEPOINT svp2 2.6. ROLLBACK TO SAVEPOINT svp1 2.7. inserts, updates, etc. Is this allowed and safe to use? What are you expecting to happen? -- When fascism comes to America, it will be intolerance sold as diversity. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] alter table alter type CASCADE
2010/5/5 Sim Zacks s...@compulab.co.il: One of the biggest problems I have maintaining a database with a lot of views is that when I want to change a datatype, I have to drop every view uses the column and every view that uses those views etc... This turns into a maintenance nightmare. Then I would question your approach to maintenance. In the past when I've had to deal with this type of thing, all views were created from a script. Edit the script, run the script, you're done. Do it in a transaction and if there are any errors nothing changes. Script looks something like: begin; drop view x1; drop view y1; ... more drop statements create view x1... create view y1 ... commit; -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] temp sequence
2010/5/5 Sim Zacks s...@compulab.co.il:
I am using 8.2.14
I am trying to use a temp sequence in a function and I'm having a lot of
trouble.
I create the temp sequence and then I have to drop it at the end of the
function, because it stays alive for the whole session and not just the
function.
I want to use the nextval function in an update statement so it gives a
sequence number to each row it updates.
If I drop the sequence and recreate it I get an OID not found error.
I tried to run the update statement dynamically, but part of the update
stmt is an int array variable and it won't cast it to text.
My goal in the end is that every row that is updated will be numbered
sequentially per update.
Do you have any ideas?
Can you manipulate the sequence instead of dropping it? i.e. select
setval('seqname',1); kind of thing?
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] temp sequence
-BEGIN PGP SIGNED MESSAGE- Hash: RIPEMD160 I am trying to use a temp sequence in a function and I'm having a lot of trouble. I create the temp sequence and then I have to drop it at the end of the function, because it stays alive for the whole session and not just the function. I want to use the nextval function in an update statement so it gives a sequence number to each row it updates. If I drop the sequence and recreate it I get an OID not found error. I tried to run the update statement dynamically, but part of the update stmt is an int array variable and it won't cast it to text. This last bit is probably solveable: can you post the code? You should be able to cast the other side if not the array itself. If you are inside a function, you already have built in iteration tools, so you may not even need to use a sequence at all. Again, seeing some code would go a long way here. - -- Greg Sabino Mullane g...@turnstep.com End Point Corporation http://www.endpoint.com/ PGP Key: 0x14964AC8 201005050928 http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8 -BEGIN PGP SIGNATURE- iEYEAREDAAYFAkvhcwIACgkQvJuQZxSWSshkmACgtAXOeMoRaED4hKMk3SCHS4bf cDIAoNvJnWZ+FtNUZiUXm7YCW8gksZuu =DU+w -END PGP SIGNATURE- -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] database Benchmark, TPC, PostgreSQL and TPC-E
On 5/5/2010 2:56 AM, williamk...@libero.it wrote: Looking for database benchmarks I fell into the Transaction Processing Performance Council Some questions on it. are there somewhere some results of TPC-* benchmarks for PostgreSQL? Are there reliable database tests? There are open source implementation? There are other benchmarks that would be worth to consider to use, or implement? Looking on the TPC-E 1.10.0 specification history I saw that Enterprise DB has quitted the membership list (14 march 2008) What does it mean for PostgreSQL? thank you Davide I think TPC is a measure of how much money a company will spend to be listed as #1. I doubt you'll find PG because you have to pay mucho $$$ to setup/run/publish TPC results. Really, though, the best benchmark you can run, is your own workload. Different benchmarks are setup to test different things. Your workload may resemble one of them closely, in which case you are in luck... but otherwise those benchmarks wont tell you much about your own workload. There is a lot of talk about benchmarks in the mailing lists... I've not read this one, but seems to match: http://www.mail-archive.com/pgsql-general@postgresql.org/msg128869.html -Andy -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] temp sequence
On 5/5/2010 2:36 AM, Sim Zacks wrote: I am using 8.2.14 I am trying to use a temp sequence in a function and I'm having a lot of trouble. I create the temp sequence and then I have to drop it at the end of the function, because it stays alive for the whole session and not just the function. I want to use the nextval function in an update statement so it gives a sequence number to each row it updates. If I drop the sequence and recreate it I get an OID not found error. I tried to run the update statement dynamically, but part of the update stmt is an int array variable and it won't cast it to text. My goal in the end is that every row that is updated will be numbered sequentially per update. Do you have any ideas? Sim Perhaps you can rewrite to use generate_series instead? http://www.postgresql.org/docs/8.0/interactive/functions-srf.html -Andy -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Multilevel partitioning
I create partitions as below. And run query: select * from test where grupa='A'; Is planner skip to analyze partitions: test2d, test2e, test2f ? best regards - CREATE TABLE test ( grupa character varying(1), id integer, num integer NOT NULL ); CREATE TABLE test1 (CONSTRAINT ABC CHECK (((grupa = 'A'::text) AND (grupa = 'C'::text INHERITS (test); CREATE TABLE test1a (CONSTRAINT A CHECK ((grupa = 'A'::text))) INHERITS (test1); CREATE TABLE test1b (CONSTRAINT B CHECK ((grupa = 'B'::text))) INHERITS (test1); CREATE TABLE test1c (CONSTRAINT C CHECK ((grupa = 'C'::text))) INHERITS (test1); CREATE TABLE test2 (CONSTRAINT DEF CHECK (((grupa = 'D'::text) AND (grupa = 'F'::text INHERITS (test); CREATE TABLE test2d (CONSTRAINT D CHECK ((grupa = 'D'::text))) INHERITS (test2); CREATE TABLE test2e (CONSTRAINT E CHECK ((grupa = 'E'::text))) INHERITS (test2); CREATE TABLE test2f (CONSTRAINT F CHECK ((grupa = 'F'::text))) INHERITS (test2);
[GENERAL] dynamically access columns in trigger
Hy, i need to have some idea how to dynamically access columns in a trigger. Use/Case: We have a workflowmodule. In different cases different workflows should be fired. In the Workflowdefinitiontable, we say tablename, when. so e.g. ~~table 1 - insert~~ ~~table 2 - update~~. We have a central trigger that fires on all tables. Inside the trigger i evaluate with TG_REL_NAME=wf_tablename - start workflow. Now the problem is that there are different workflows in the update case. e.g. if the price changes, a CheckNewPrice workflow is startet, if a date changes a CheckNewDeliveryDate workflow is started. My idea is to give my workflowdefinitiontable a additional column the holds the condition, e.g. ~~table 2 - update - ~ new.pricecolumnold.pricecolumn ~~~ so i need a solution how to evaluate that dynamic statement in my global trigger funktion. Any ideas? -- Daniel Schuchardt /Softwareentwicklung/ /http://www.prodat-sql.de/ -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Table alias and inherited tables
Hi, I notice that for the following: Table Base with say columns col1, col2 Table Child1 inherits(Base) Table Child2 inherits(Base) With check constraints and rules to insert the rows to appropriate tables defined. Inserts work as expected. select * from Base; returns rows from both the children But something like select b from base b where b.col1=condition; returns 0 rows even when there are rows in one of the child tables that satisfy the condition. Is this expected ? thx - Amol -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] need help on postgres
hi, i am a student , and for my final project i have to work with postgresql ; my project subject is data warehousing data mining, so i need to define cube; create dimention table fact table ... i had active cube in template1; i mean i run cube.sql and there are cube functions in my databases but i don't know how to create cube!!! please help me and explain me how to do these kind of things step by step!! With Best Regards -- Raha Sadeghi
Re: [GENERAL] Table alias and inherited tables
nevermind.. guess there was a problem with the query condition itself regards - Amol On 05/04/10 22:42, Amol Chiplunkar wrote: Hi, I notice that for the following: Table Base with say columns col1, col2 Table Child1 inherits(Base) Table Child2 inherits(Base) With check constraints and rules to insert the rows to appropriate tables defined. Inserts work as expected. select * from Base; returns rows from both the children But something like select b from base b where b.col1=condition; returns 0 rows even when there are rows in one of the child tables that satisfy the condition. Is this expected ? thx - Amol -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] need help on postgres
On 5 May 2010 08:28, raha sadeghi raha.sade...@gmail.com wrote: hi, i am a student , and for my final project i have to work with postgresql ; my project subject is data warehousing data mining, so i need to define cube; create dimention table fact table ... i had active cube in template1; i mean i run cube.sql and there are cube functions in my databases but i don't know how to create cube!!! please help me and explain me how to do these kind of things step by step!! With Best Regards -- Raha Sadeghi PostgreSQL doesn't currently support CUBE (not to be confused with the cube contrib module). You may be able to implement what you are trying to achieve by using common table expressions: http://www.postgresql.org/docs/8.4/static/queries-with.html This is only available in PostgreSQL 8.4 onward. Regards Thom
Re: [GENERAL] database Benchmark, TPC, PostgreSQL and TPC-E
williamk...@libero.it wrote:
are there somewhere some results of TPC-* benchmarks for PostgreSQL?
Are there reliable database tests? There are open source implementation?
There are other benchmarks that would be worth to consider to use, or
implement?
There's a bunch of information on this topic at
http://wiki.postgresql.org/wiki/Category:Benchmarking ; the entries for
DBT-{2,3,5} are open source implementations of some of those. And the
links at the top there point to info about the sole audited PostgreSQL
benchmark I'm aware of, which is a SPEC result rather than a TPC one.
Looking on the TPC-E 1.10.0 specification history I saw that Enterprise DB has
quitted the membership list (14 march 2008)
What does it mean for PostgreSQL?
Official TPC results require spending a lot of money, from buying
servers to having the results audited, and there's very little value
received for that expense from the perspective of the PostgreSQL
community. The fact that the software is free doesn't quite fit into
the whole performance/$ focus of what real TPC results deliver. TPC-H
is the most interesting of them to me (and that's where there's a
detailed page linking to resources related to it) because it suggests
types of queries the database could handle better than it does right now.
--
Greg Smith 2ndQuadrant US Baltimore, MD
PostgreSQL Training, Services and Support
g...@2ndquadrant.com www.2ndQuadrant.us
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] question about log entry from trigger execution
I have a database I inherited with a number of triggers defined on various tables. One AFTER trigger, when executed, causes a database log entry with a CONTEXT entry, but no ERROR. I normally only see the CONTEXT entries when an ERROR occurs. The trigger is on table rbs, and it returns NULL. Here are the entries (slightly edited): May 5 16:08:21 postgres[18723]: [598-1] 2010-05-05 16:08:21 UTC LOG: statement: DELETE FROM rbs WHERE id = 136259855 AND cid = 601 May 5 16:08:21 postgres[18723]: [599-3] 2010-05-05 16:08:21 UTC CONTEXT: SQL statement UPDATE uinfo UI SET id_bitmap = (id_bitmap ~(1::bit(128) (C.bitmap_index May 5 16:08:21 postgres[18723]: [599-4] - 1)) ) FROM cinfo C WHERE UI.id = $1 AND $2 = C.cid May 5 16:08:21 postgres[18723]: [599-5] ^IPL/pgSQL function rbs__adel line 6 at SQL statement May 5 16:08:21 postgres[18723]: [600-1] 2010-05-05 16:08:21 UTCLOG: statement: COMMIT The statement that seems to be logged is: UPDATE uinfo UI SET id_bitmap = (id_bitmap ~(1::bit(128) (C.bitmap_index - 1)) ) FROM cinfo C WHERE UI.id = OLD.id AND OLD.cid = C.cid; The update seems to get done ok. I still wonder what the log entry is trying to tell me. This is PostgreSQL 8.3. Any ideas? Susan
Re: [GENERAL] question about log entry from trigger execution
Susan Cassidy scass...@stbernard.com writes: I have a database I inherited with a number of triggers defined on various tables. One AFTER trigger, when executed, causes a database log entry with a CONTEXT entry, but no ERROR. I normally only see the CONTEXT entries when an ERROR occurs. The CONTEXT is associated with the preceding LOG line, in this case. It's not at all true that CONTEXT is only attached to ERROR --- it applies to any level of log message. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] pg_class has 3615 rows and 1010Mb in table size
The pgAdmin performance on one of our database servers has been dismal
for a while now. I captured one of the queries that was taking forever
to return, therefore making pgAdmin unresponsive for up to 10 minutes.
The query is as follows:
SELECT rel.oid, relname, rel.reltablespace AS spcoid, spcname,
pg_get_userbyid(relowner) AS relowner, relacl, relhasoids,
relhassubclass, reltuples, description, conname, conkey,
EXISTS(select 1 FROM pg_trigger
JOIN pg_proc pt ON pt.oid=tgfoid AND
pt.proname='logtrigger'
JOIN pg_proc pc ON
pc.pronamespace=pt.pronamespace AND pc.proname='slonyversion'
WHERE tgrelid=rel.oid) AS isrepl
, substring(array_to_string(rel.reloptions, ',') from
'fillfactor=([0-9]*)') AS fillfactor
FROM pg_class rel
LEFT OUTER JOIN pg_tablespace ta on ta.oid=rel.reltablespace
LEFT OUTER JOIN pg_description des ON (des.objoid=rel.oid AND
des.objsubid=0)
LEFT OUTER JOIN pg_constraint c ON c.conrelid=rel.oid AND
c.contype='p'
WHERE relkind IN ('r','s','t') AND relnamespace = 16686::oid
ORDER BY relname
I looked at the pg_class table and noticed that its size is 1010Mb and
index size is 1137Mb, while the table itself has only 3615 rows in it.
I tried vacuuming it, but that did not change anything. Is there
anything I can do to get this table back to the size it is supposed to
be at?
PostgreSQL 8.2.6 on Windows 2003 Server.
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Please help me debug regular segfaults on 8.3.10
Alvaro Herrera wrote: pgsql wrote: Hi, one of our pgsql instances recently started to segfault multiple times a week. I tried a couple of things to pin it down to a certain query or job but failed to find any pattern. All I can offer is some notes and a set of similar looking back traces. Please install the debuginfo package(s). Have you got some external module installed? ..for whatever reason I cannot get the postmaster provided by postgresql-debuginfo-8.3.10-2PGDG.el5.x86_64.rpm to run; it immediately causes a segfault: ld-linux-x86-64[4286] general protection rip:3b2ca06471 rsp:7fff9a5077c0 error:0 However I just build 8.3.10 from source with debug enabled. As soon as it crashes I'll post the new back trace. Thanks! -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Please help me debug regular segfaults on 8.3.10
Tom Lane wrote: pgsql pg...@lavabit.com writes: Looking at debug_query_string in the core dumps would at least show what SQL command is calling the function(s) --- and I wouldn't be surprised if there's exactly one function involved here. Content of debug_query_string: core.21207 $1 = 63106368 core.20832 $1 = 292449712 core.25421 $1 = 292450320 core.23631 $1 = 29245 core.9419 $1 = 284979152 core.16801 $1 = 284978992 core.32242 $1 = 284971248 core.10776 $1 = 284978832 As per Alvaro's suggestion, installing postgresql-debuginfo would make the stack traces a lot more useful, too. Build from source (without the relcache patch) with debug enabled; waiting for the next crash. Thank you -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] pg_class has 3615 rows and 1010Mb in table size
On Wed, 2010-05-05 at 09:40 -0700, Nikola wrote: I looked at the pg_class table and noticed that its size is 1010Mb and index size is 1137Mb, while the table itself has only 3615 rows in it. I tried vacuuming it, but that did not change anything. Is there anything I can do to get this table back to the size it is supposed to be at? PostgreSQL 8.2.6 on Windows 2003 Server. vacuum full, its an exclusive lock though. I don't recall if 8.2 autovacuum would vacuum system catalogs or not. Either way, you really should upgrade, especially since you are on Windows. Joshua D. Drake -- PostgreSQL.org Major Contributor Command Prompt, Inc: http://www.commandprompt.com/ - 503.667.4564 Consulting, Training, Support, Custom Development, Engineering -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Please help me debug regular segfaults on 8.3.10
pgsql pg...@lavabit.com writes: Tom Lane wrote: Looking at debug_query_string in the core dumps would at least show what SQL command is calling the function(s) --- and I wouldn't be surprised if there's exactly one function involved here. Content of debug_query_string: core.21207 $1 = 63106368 Um, that's not too helpful, we want to see the string it's pointing at. In a non-debug build you probably need to say p (char *) debug_query_string or something like that. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] LDAP: Auto user creation and role membership
Hi, I have Postgres 8.4.3 running with gss authentication against Fedora's FreeIPA (Integrated Kerberos, LDAP and some other services). I would like to auto-create users and auto-map postgres roles with users and groups within the FreeIPA LDAP directory. Can anyone tell me if this is available in Postgres? Looking through the docs, it appears that it is not, and I have to manage user creation and role membership manually. I found this post from 5 years ago: http://www.mail-archive.com/pgsql-hack...@postgresql.org/msg58156.html But I haven't found anything mentioning this functionality since. Is anyone else attempting to do this? I could probably write a script or something to auto create postgres users from the LDAP directory (and remove users who have been deleted from LDAP) and then synchronise the roles with LDAP groups, but I don't want to repeat this work if someone's already done it. I'd also appreciate any hints or suggestions for ways to do this. Thanks, Dan -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] when to update pg_statistic relation?
when to update pg_statistic relation? is it when inserting a new tuple of any user's relations? and the relation pg_stats at document 8.4 chapter 44.55.pg_stats hasn't been used anymore ? another question is: I noticed when i send the sql :select catcode from pois goup by catcode, the function :double estimate_num_groups(PlannerInfo *root, List *groupExprs, double input_rows) uses pg_statistic.staattnum to set Agg.numGroups, then if the pg_statistic.staattnum is incorrect ,does postgresql still use this infomation to set Agg.numGroups ?
Re: [GENERAL] LDAP: Auto user creation and role membership
On Wed, May 5, 2010 at 22:49, Daniel Scott djsc...@mit.edu wrote: Hi, I have Postgres 8.4.3 running with gss authentication against Fedora's FreeIPA (Integrated Kerberos, LDAP and some other services). I would like to auto-create users and auto-map postgres roles with users and groups within the FreeIPA LDAP directory. Can anyone tell me if this is available in Postgres? Looking through the docs, it appears that it is not, and I have to manage user creation and role membership manually. I found this post from 5 years ago: http://www.mail-archive.com/pgsql-hack...@postgresql.org/msg58156.html But I haven't found anything mentioning this functionality since. Is anyone else attempting to do this? I could probably write a script or something to auto create postgres users from the LDAP directory (and remove users who have been deleted from LDAP) and then synchronise the roles with LDAP groups, but I don't want to repeat this work if someone's already done it. I'd also appreciate any hints or suggestions for ways to do this. I've written scripts to do this several times using both python and perl. It's pretty simple. I haven't made them generic though, so it's not something I can share. But just a tool that compares the list of users and issues the appropriate CREATE USER or DROP USER commands is pretty trivial. Granting role permissions adds a bit of complexity, but not much. Trying to do them generic will make it a lot more complex though, so if you jus tneed it for this one case, a quick one-off script is probably the easiest way to go. -- Magnus Hagander Me: http://www.hagander.net/ Work: http://www.redpill-linpro.com/ -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] pg_class has 3615 rows and 1010Mb in table size
On May 5, 3:37 pm, j...@commandprompt.com (Joshua D. Drake) wrote: On Wed, 2010-05-05 at 09:40 -0700, Nikola wrote: I looked at the pg_class table and noticed that its size is 1010Mb and index size is 1137Mb, while the table itself has only 3615 rows in it. I tried vacuuming it, but that did not change anything. Is there anything I can do to get this table back to the size it is supposed to be at? PostgreSQL 8.2.6 on Windows 2003 Server. vacuum full, its an exclusive lock though. I don't recall if 8.2 autovacuum would vacuum system catalogs or not. Either way, you really should upgrade, especially since you are on Windows. Joshua D. Drake -- PostgreSQL.org Major Contributor Command Prompt, Inc:http://www.commandprompt.com/- 503.667.4564 Consulting, Training, Support, Custom Development, Engineering -- Sent via pgsql-general mailing list (pgsql-gene...@postgresql.org) To make changes to your subscription:http://www.postgresql.org/mailpref/pgsql-general Yes, I was hoping not to have to do a vacuum full due to the exclusive lock. However, if that's the only solution (other than upgrading in general) then that's what needs to happen. Hopefully, vacuum full does not end up taking an extremely long time to run. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] pg_class has 3615 rows and 1010Mb in table size
Excerpts from Joshua D. Drake's message of mié may 05 15:37:05 -0400 2010: On Wed, 2010-05-05 at 09:40 -0700, Nikola wrote: I looked at the pg_class table and noticed that its size is 1010Mb and index size is 1137Mb, while the table itself has only 3615 rows in it. I tried vacuuming it, but that did not change anything. Is there anything I can do to get this table back to the size it is supposed to be at? PostgreSQL 8.2.6 on Windows 2003 Server. vacuum full, its an exclusive lock though. Note though that there being such few tuples, it should be rather quick. I don't recall if 8.2 autovacuum would vacuum system catalogs or not. Eh, of course it would. -- -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] alter table alter type CASCADE
On Wed, May 5, 2010 at 7:31 AM, Thom Brown thombr...@gmail.com wrote: On 5 May 2010 13:14, Chris Barnes compuguruchrisbar...@hotmail.com wrote: It has been some years since I worked with Oracle, doesn't Oracle recompile the view when the object it references changes in structure? Send What does Oracle do when you've got a view like: CREATE OR REPLACE VIEW test_view AS SELECT test.test_id, test.test_value, date_is_future(test.test_date) as upcoming FROM test; Where date_is_future expects a text field, but you change the test table so that test_date is now varchar(20)? The function no longer matches the signature. Does it just prevent it? And what about when conditions are provided in a WHERE clause which become invalid when the column type changes? If Oracle can find a function that will make the view valid it will continue to allow usage of the view; though it is still marked as needing to be rebuilt from a user perspective any calls to it will succeed if there is a possible way for the view to still be valid. If there is no possible way for any use of the view to succeed then the calls fail. -- Peter Hunsberger -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Savepoint and prepared transactions
yep, thank you! On Wed, May 5, 2010 at 5:47 AM, Scott Marlowe scott.marl...@gmail.comwrote: Should work. I'm not sure 2.5 release savepoint is necessary. On Wed, May 5, 2010 at 12:53 AM, Konstantin Izmailov pgf...@gmail.com wrote: I expect that only changes on step 2.7 persisted in DB. On Tue, May 4, 2010 at 11:18 PM, Scott Marlowe scott.marl...@gmail.com wrote: On Tue, May 4, 2010 at 11:52 PM, Konstantin Izmailov pgf...@gmail.com wrote: Hi, I'm working on a solution that utilizes 2 phase commit protocol (between SQL Server and PostgreSQL). Normally PostgreSQL statements sequense is: 1. START 2. inserts, updates, etc. 3. PREPARE TRANSACTION 'uuid' 4. COMMIT PREPARED 'uuid' What if on step 2 user application issues statements with SAVEPOINTs, e.g. 2.1. SAVEPOINT svp1 2.2. inserts, updates, etc. 2.3. SAVEPOINT svp2 2.4. inserts, updates, etc. 2.5. RELEASE SAVEPOINT svp2 2.6. ROLLBACK TO SAVEPOINT svp1 2.7. inserts, updates, etc. Is this allowed and safe to use? What are you expecting to happen? -- When fascism comes to America, it will be intolerance sold as diversity.
Re: [GENERAL] Please help me debug regular segfaults on 8.3.10
Tom Lane wrote: pgsql pg...@lavabit.com writes: Tom Lane wrote: Looking at debug_query_string in the core dumps would at least show what SQL command is calling the function(s) --- and I wouldn't be surprised if there's exactly one function involved here. Content of debug_query_string: core.21207 $1 = 63106368 Um, that's not too helpful, we want to see the string it's pointing at. Sorry about that. All statements are calling one of two pl/pgsql functions. While that information already helps me a lot, it'll take me a while to step through the code. Those functions are outer wrappers calling many other procedures. Thank you very much. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] how to get the deleted data
Unauthorized user deleted some critical data from the database. I don't have any log. Is it possible to get what was the command used to delete the data?
Re: [GENERAL] how to get the deleted data
On 06/05/2010, AI Rumman rumman...@gmail.com wrote: Unauthorized user deleted some critical data from the database. I don't have any log. Is it possible to get what was the command used to delete the data? Is your server saving WAL's? -- Brian Modra Land line: +27 23 5411 462 Mobile: +27 79 69 77 082 5 Jan Louw Str, Prince Albert, 6930 Postal: P.O. Box 2, Prince Albert 6930 South Africa http://www.zwartberg.com/ -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
