Re: [GENERAL] Recursion in triggers?
On 24 Jan 2010, at 5:36, Gauthier, Dave wrote: Hi: I’m dealing with a hierarchical design where changes in one record can and should cause changes in other records lower inthe hierarchy. I’m trying to use update triggers to do this. And recursion would be a real nice way to do this. What I need to know is if, in the “after” update trigger I make the subsequent updates to other records in the same table, with the OLD/NEW record ponters be set properly in those subsequent update trigger invocations? Will the current and modified NEW.* values be passed down into the next update trigger “before” call as OLD.* values? Or is recursion like this not allowed? I'm not really sure what you're trying to do, so it's a tad hard to answer. Are you using multiple before-update triggers on the SAME table? In that case you ask an interesting question that I don't know the answer to either. I do know that they'll fire ordered alphabetically on trigger name. A test case with a few raise notices is easily created though: BEGIN; CREATE FUNCTION x() RETURNS trigger AS $$ BEGIN RAISE NOTICE 'OLD.test = %, NEW.test = %', OLD.test, NEW.test; NEW.test := New.test + 1; RETURN NEW; END; $$ LANGUAGE plpgsql; CREATE TABLE trigger_test(test int); INSERT INTO trigger_test VALUES (1); CREATE TRIGGER a BEFORE UPDATE ON trigger_test FOR EACH ROW EXECUTE PROCEDURE x(); CREATE TRIGGER b BEFORE UPDATE ON trigger_test FOR EACH ROW EXECUTE PROCEDURE x(); SET client_min_messages TO notice; UPDATE trigger_test SET test=2 WHERE test=1; ROLLBACK; development= \i /tmp/trigger_test.sql BEGIN CREATE FUNCTION CREATE TABLE INSERT 0 1 CREATE TRIGGER CREATE TRIGGER SET psql:/tmp/trigger_test.sql:26: NOTICE: OLD.test = 1, NEW.test = 2 psql:/tmp/trigger_test.sql:26: NOTICE: OLD.test = 1, NEW.test = 3 UPDATE 1 ROLLBACK Alban Hertroys -- If you can't see the forest for the trees, cut the trees and you'll see there is no forest. !DSPAM:737,4b5c183b10607129821012! -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Questions about connection clean-up and invalid page header
Hi Everybody. I have two questions. 1. We have a system that is accessed by Crystal reports which is in turned controlled by another (3rd party) system. Now, when a report takes too long or the user cancels it, it doesn't send a cancel request to Postgres. It just kills the Crystal process that works on it. As a result, the query is left alive on the Postgres backend. Eventually I get the message Unexpected End of file and the query is cancelled. But this doesn't happen soon enough for me - these are usually very heavy queries, and I'd like them to be cleaned up as soon as possible if the client connection has ended. Is there a parameter to set in the configuration or some other means to shorten the time before an abandoned backend's query is cancelled? 2. I get the following message in my development database: vacuumdb: vacuuming of database reports failed: ERROR: invalid page header in block 6200 of relation rb I had this already a couple of months ago. Looking around the web, I saw this error is supposed to indicate a hardware error. I informed my sysadmin, but since this is just the dev system and the data was not important, I did a TRUNCATE TABLE on the rb relation, and the errors stopped... But now the error is back, and I'm a bit suspicious. If this is a hardware issue, it's rather suspicious that it returned in the exact same relation after I did a truncate table. I have many other relations in the system, ones that fill up a lot faster. So I suspect this might be a PostgreSQL issue after all. What can I do about this? We are currently using PostgreSQL v. 8.3.1 on the server side. TIA, Herouth -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Self-referential records
Assuming I have the following table:
CREATE TABLE refers (
idSERIAL PRIMARY KEY,
name VARCHAR(255) NOT NULL,
parent_id INTEGER NOT NULL,
FOREIGN KEY (parent_id) REFERENCES refers(id)
);
I need to insert two records so that select * from refers looks like this:
=# select * from refers;
id | name | parent_id
+--+---
1 | | 1
2 | yyy | 2
The first record can't be inserted because I don't yet know the parent_id. The
second record can be inserted after the first, but I since this is merely a
large .sql file that I intend to shove into the PG, I'd much rather declare a
variable in the script to get this done. I'm thinking something like the
following pseudo-code:
INSERT INTO refers (name, parent_id) VALUES ('', :id);
SELECT id INTO :parent_id FROM refers WHERE name='';
INSERT INTO refers (name, parent_id) VALUES ('yyy', :parent_id);
Obviously the above is gibberish, but hopefully it makes clear what I'm trying
to do :)
Oh, and parent_id is NOT NULL because I hate the logical inconsistencies
associated with NULL values.
Cheers,
Ovid--
Buy the book - http://www.oreilly.com/catalog/perlhks/
Tech blog- http://use.perl.org/~Ovid/journal/
Twitter - http://twitter.com/OvidPerl
Official Perl 6 Wiki - http://www.perlfoundation.org/perl6
--
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] Self-referential records
On Sunday 24. January 2010 14.43.10 Ovid wrote: Assuming I have the following table: CREATE TABLE refers ( idSERIAL PRIMARY KEY, name VARCHAR(255) NOT NULL, parent_id INTEGER NOT NULL, FOREIGN KEY (parent_id) REFERENCES refers(id) ); I need to insert two records so that select * from refers looks like this: =# select * from refers; id | name | parent_id +--+--- 1 | | 1 2 | yyy | 2 The first record can't be inserted because I don't yet know the parent_id. I've got a similar structure. I just declared the root node with both id and parent_id=0. regards, -- Leif Biberg Kristensen http://solumslekt.org/ -- 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] Self-referential records
On 1/24/10 8:43 AM, Ovid wrote:
Assuming I have the following table:
CREATE TABLE refers (
idSERIAL PRIMARY KEY,
name VARCHAR(255) NOT NULL,
parent_id INTEGER NOT NULL,
FOREIGN KEY (parent_id) REFERENCES refers(id)
);
I need to insert two records so that select * from refers looks like this:
=# select * from refers;
id | name | parent_id
+--+---
1 | | 1
2 | yyy | 2
The first record can't be inserted because I don't yet know the parent_id. The
second record can be inserted after the first, but I since this is merely a
large .sql file that I intend to shove into the PG, I'd much rather declare a
variable in the script to get this done. I'm thinking something like the
following pseudo-code:
INSERT INTO refers (name, parent_id) VALUES ('', :id);
SELECT id INTO :parent_id FROM refers WHERE name='';
INSERT INTO refers (name, parent_id) VALUES ('yyy', :parent_id);
Obviously the above is gibberish, but hopefully it makes clear what I'm trying
to do :)
Oh, and parent_id is NOT NULL because I hate the logical inconsistencies
associated with NULL values.
You could always remove the NOT NULL or the FOREIGN KEY constraints
during data load, then add them back on afterward.
If the problem is with everyday usage and not just data load, you
can still do this trick, since DDL can be transactionalized (is
that a word). Just start a transaction, remove the NOT NULL
constraint, add your new records, then update the parent_key as
appropriate, then add the NOT NULL back. If any point during the
process fails, just rollback the transaction. You may want to
set the isolation level to serializable, but I'm not sure if
that's necessary.
-Bill
--
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] Self-referential records
Ovid wrote on 24.01.2010 14:43: Assuming I have the following table: CREATE TABLE refers ( idSERIAL PRIMARY KEY, name VARCHAR(255) NOT NULL, parent_id INTEGER NOT NULL, FOREIGN KEY (parent_id) REFERENCES refers(id) ); I need to insert two records so that select * from refers looks like this: =# select * from refers; id | name | parent_id +--+--- 1 | | 1 2 | yyy | 2 The first record can't be inserted because I don't yet know the parent_id. I ususally identify the root record by setting the parent_id to NULL. In my experience creating a cycle in the tree creates a lot of trouble that is hard to come by. Thomas -- 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 use PG_DUMP?
Hi, I'am having trouble using PG_DUMP. The problem is the following, I have made some minor changes to my database, I have added one table. I have generated the CREATE statements using a modeling tool, and I have created another database to test the changes. My problem is the order of the INSERT statements generated by PG_DUMP [-a -d], causing errors because of the order of the INSERTS. I have tested PG_DUMP [-a] that creates the COPY statements, but no data is added to the tables. How can I solve this? Best Regards,
Re: [GENERAL] Self-referential records
Ovid curtis_ovid_...@yahoo.com wrote:
Assuming I have the following table:
CREATE TABLE refers (
idSERIAL PRIMARY KEY,
name VARCHAR(255) NOT NULL,
parent_id INTEGER NOT NULL,
FOREIGN KEY (parent_id) REFERENCES refers(id)
);
I need to insert two records so that select * from refers looks like this:
=# select * from refers;
id | name | parent_id
+--+---
1 | | 1
2 | yyy | 2
I think you mean id=1, parent_id=2 and id=2, parent_id=1, or?
The first record can't be inserted because I don't yet know the parent_id.
The second record can be inserted after the first, but I since this is merely
a large .sql file that I intend to shove into the PG, I'd much rather declare
a variable in the script to get this done. I'm thinking something like the
following pseudo-code:
INSERT INTO refers (name, parent_id) VALUES ('', :id);
SELECT id INTO :parent_id FROM refers WHERE name='';
INSERT INTO refers (name, parent_id) VALUES ('yyy', :parent_id);
Obviously the above is gibberish, but hopefully it makes clear what I'm
trying to do :)
Oh, and parent_id is NOT NULL because I hate the logical inconsistencies
associated with NULL values.
To handle that you can set the constzraint deferrable, initially
deferred:
test=# CREATE TABLE refers ( id SERIAL PRIMARY KEY, name VARCHAR(255) NOT
NULL, parent_id INTEGER NOT NULL, FOREIGN KEY (parent_id) REFERENCES refers(id)
deferrable initially deferred);
NOTICE: CREATE TABLE will create implicit sequence refers_id_seq for serial
column refers.id
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index refers_pkey
for table refers
CREATE TABLE
Zeit: 25,599 ms
test=*# insert into refers (name, parent_id) values ('xxx',0);
INSERT 0 1
Zeit: 0,662 ms
test=*# insert into refers (name, parent_id) select 'yyy', id from refers where
name = 'xxx';
INSERT 0 1
Zeit: 0,436 ms
test=*# update refers set parent_id = (select id from refers where name =
'yyy') where name = 'xxx';
UPDATE 1
Zeit: 0,431 ms
test=*# select * from refers;
id | name | parent_id
+--+---
2 | yyy | 1
1 | xxx | 2
(2 Zeilen)
The next release 9.0 contains (i hope) writes CTE, with this featue you can do:
test=# CREATE TABLE refers ( id SERIAL PRIMARY KEY, name VARCHAR(255) NOT
NULL, parent_id INTEGER NOT NULL, FOREIGN KEY (parent_id) REFERENCES refers(id)
deferrable initially deferred);
NOTICE: CREATE TABLE will create implicit sequence refers_id_seq for serial
column refers.id
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index refers_pkey
for table refers
CREATE TABLE
Time: 3,753 ms
test=*#
with
t1 as (select nextval('refers_id_seq') as id),
t2 as (insert into refers (id, name, parent_id) select
nextval('refers_id_seq'), 'yyy', t1.id from t1 returning *),
t3 as (insert into refers (id, name, parent_id) select t1.id, 'xxx', t2.id
from t1, t2)
select true;
bool
--
t
(1 row)
Time: 0,853 ms
test=*# select * from refers;
id | name | parent_id
+--+---
2 | yyy | 1
1 | xxx | 2
(2 rows)
That's (the two insert's) are now one single statement ;-)
Andreas
--
Really, I'm not out to destroy Microsoft. That will just be a completely
unintentional side effect. (Linus Torvalds)
If I was god, I would recompile penguin with --enable-fly. (unknown)
Kaufbach, Saxony, Germany, Europe. N 51.05082°, E 13.56889°
--
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] Self-referential records
Xi Shen davidshe...@googlemail.com wrote:
To handle that you can set the constzraint deferrable, initially
deferred:
test=# CREATE TABLE refers ( id SERIAL PRIMARY KEY, name VARCHAR(255) NOT
NULL, parent_id INTEGER NOT NULL, FOREIGN KEY (parent_id) REFERENCES
refers(id) deferrable initially deferred);
NOTICE: CREATE TABLE will create implicit sequence refers_id_seq for
serial column refers.id
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index
refers_pkey for table refers
CREATE TABLE
Zeit: 25,599 ms
test=*# insert into refers (name, parent_id) values ('xxx',0);
INSERT 0 1
Zeit: 0,662 ms
to Andreas,
this 'deferrable' thing is really cool. but i have a question. at this
point, where i insert this text, if i select the refers table, what
would the parent_id looks like?
0, as i inserted:
test=*# insert into refers (name, parent_id) values ('xxx',0);
INSERT 0 1
Zeit: 0,636 ms
test=*# select * from refers;
id | name | parent_id
+--+---
1 | xxx | 0
(1 Zeile)
Andreas
--
Really, I'm not out to destroy Microsoft. That will just be a completely
unintentional side effect. (Linus Torvalds)
If I was god, I would recompile penguin with --enable-fly. (unknown)
Kaufbach, Saxony, Germany, Europe. N 51.05082°, E 13.56889°
--
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] [HACKERS] Sugerencia de opcion
2009/1/22 Informatica-Cooperativa Cnel. Oviedo informat...@coopovie.com.py: Buenos Dias todos, Soy un usuario de postgres de Paraguay, consulto sobre la posibilidad de inclucion en la futura version la siguiente sentencia(Uso de alias en la condicion HAVING ): SELECT id, sum(salario) as SumaSalario FROM salarios GROUP BY id HAVING SumaSalario500; I've wished for that syntax once or twice myself, but I'm assuming there's a reason we haven't implemented it? Part of the problem is it's inheritantly ambiguous if salarios happens to contain a column called sumasalario, which is a problem that seems to arise for me fairly regularly in practice. Still, it would be nice for WHERE/GROUP BY/HAVING clauses to have an explicit way to reference the target list column called foo. ...Robert -- 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] Self-referential records
On Sun, Jan 24, 2010 at 10:36 PM, Andreas Kretschmer
akretsch...@spamfence.net wrote:
Ovid curtis_ovid_...@yahoo.com wrote:
Assuming I have the following table:
CREATE TABLE refers (
id SERIAL PRIMARY KEY,
name VARCHAR(255) NOT NULL,
parent_id INTEGER NOT NULL,
FOREIGN KEY (parent_id) REFERENCES refers(id)
);
I need to insert two records so that select * from refers looks like this:
=# select * from refers;
id | name | parent_id
+--+---
1 | | 1
2 | yyy | 2
I think you mean id=1, parent_id=2 and id=2, parent_id=1, or?
The first record can't be inserted because I don't yet know the parent_id.
The second record can be inserted after the first, but I since this is
merely a large .sql file that I intend to shove into the PG, I'd much rather
declare a variable in the script to get this done. I'm thinking something
like the following pseudo-code:
INSERT INTO refers (name, parent_id) VALUES ('', :id);
SELECT id INTO :parent_id FROM refers WHERE name='';
INSERT INTO refers (name, parent_id) VALUES ('yyy', :parent_id);
Obviously the above is gibberish, but hopefully it makes clear what I'm
trying to do :)
Oh, and parent_id is NOT NULL because I hate the logical inconsistencies
associated with NULL values.
To handle that you can set the constzraint deferrable, initially
deferred:
test=# CREATE TABLE refers ( id SERIAL PRIMARY KEY, name VARCHAR(255) NOT
NULL, parent_id INTEGER NOT NULL, FOREIGN KEY (parent_id) REFERENCES
refers(id) deferrable initially deferred);
NOTICE: CREATE TABLE will create implicit sequence refers_id_seq for
serial column refers.id
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index refers_pkey
for table refers
CREATE TABLE
Zeit: 25,599 ms
test=*# insert into refers (name, parent_id) values ('xxx',0);
INSERT 0 1
Zeit: 0,662 ms
to Andreas,
this 'deferrable' thing is really cool. but i have a question. at this
point, where i insert this text, if i select the refers table, what
would the parent_id looks like?
test=*# insert into refers (name, parent_id) select 'yyy', id from refers
where name = 'xxx';
INSERT 0 1
Zeit: 0,436 ms
test=*# update refers set parent_id = (select id from refers where name =
'yyy') where name = 'xxx';
UPDATE 1
Zeit: 0,431 ms
test=*# select * from refers;
id | name | parent_id
+--+---
2 | yyy | 1
1 | xxx | 2
(2 Zeilen)
The next release 9.0 contains (i hope) writes CTE, with this featue you can
do:
test=# CREATE TABLE refers ( id SERIAL PRIMARY KEY, name VARCHAR(255) NOT
NULL, parent_id INTEGER NOT NULL, FOREIGN KEY (parent_id) REFERENCES
refers(id) deferrable initially deferred);
NOTICE: CREATE TABLE will create implicit sequence refers_id_seq for
serial column refers.id
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index refers_pkey
for table refers
CREATE TABLE
Time: 3,753 ms
test=*#
with
t1 as (select nextval('refers_id_seq') as id),
t2 as (insert into refers (id, name, parent_id) select
nextval('refers_id_seq'), 'yyy', t1.id from t1 returning *),
t3 as (insert into refers (id, name, parent_id) select t1.id, 'xxx', t2.id
from t1, t2)
select true;
bool
--
t
(1 row)
Time: 0,853 ms
test=*# select * from refers;
id | name | parent_id
+--+---
2 | yyy | 1
1 | xxx | 2
(2 rows)
That's (the two insert's) are now one single statement ;-)
Andreas
--
Really, I'm not out to destroy Microsoft. That will just be a completely
unintentional side effect. (Linus Torvalds)
If I was god, I would recompile penguin with --enable-fly. (unknown)
Kaufbach, Saxony, Germany, Europe. N 51.05082°, E 13.56889°
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
to Ovid,
if you are going to create a tree in the table, you should certainly
allow the parent_id be null, otherwise, what would be the parent of
root? if you are meant to create a circle...god, what is that for? may
i have your story?
--
Best Regards,
David Shen
http://twitter.com/davidshen84/
http://meme.yahoo.com/davidshen84/
--
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] Self-referential records
What is the preferred way to enforce that there is at least one orphan record if any at all, and that a record is not a Marty McFly type descendent of itself? I would suggest that a statement level after trigger is the way to go, but I myself have never actually had to enforce this. Regards, Peter Geoghegan -- 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] Self-referential records
Xi Shen davidshe...@googlemail.com wrote:
what if you insert other values like '1', '999'? will the insertion
successful? if so, what's the difference between a deferred reference
and no reference at all?
Nice question ;-)
Okay, recreate the table but without NOT NULL:
test=# CREATE TABLE refers ( id SERIAL PRIMARY KEY, name VARCHAR(255) NOT
NULL, parent_id INTEGER , FOREIGN KEY (parent_id) REFERENCES refers(id)
deferrable initially deferred);
NOTICE: CREATE TABLE will create implicit sequence refers_id_seq for serial
column refers.id
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index refers_pkey
for table refers
CREATE TABLE
Zeit: 63,477 ms
test=*# insert into refers (name, parent_id) values ('xxx',null);
INSERT 0 1
Zeit: 0,686 ms
I think, NULL is more sensible than inserting a 'random' value like 0.
Andreas
--
Really, I'm not out to destroy Microsoft. That will just be a completely
unintentional side effect. (Linus Torvalds)
If I was god, I would recompile penguin with --enable-fly. (unknown)
Kaufbach, Saxony, Germany, Europe. N 51.05082°, E 13.56889°
--
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] Self-referential records
If you do not use null to represent a root node, when you go to unwind the
data from the table to generate a hierarchy tree, you could end up with an
infinite loop. The query will always be looking for the next parent in the
hierarchy. Meaning, you will want to find the parent of a node using the
given parent_id, the query will find the parent of 1 to be 1, then it will
look again for the parent of 1 it will find 1, etc. etc. ad nauseum. I enjoy
using recursion as much as the next guy, but this could cause some serious
issues with the PostgreSQL query engine eating up system resources.
Wayne E. Pfeffer
On Sun, Jan 24, 2010 at 9:13 AM, Andreas Kretschmer
akretsch...@spamfence.net wrote:
Xi Shen davidshe...@googlemail.com wrote:
what if you insert other values like '1', '999'? will the insertion
successful? if so, what's the difference between a deferred reference
and no reference at all?
Nice question ;-)
Okay, recreate the table but without NOT NULL:
test=# CREATE TABLE refers ( id SERIAL PRIMARY KEY, name VARCHAR(255) NOT
NULL, parent_id INTEGER , FOREIGN KEY (parent_id) REFERENCES refers(id)
deferrable initially deferred);
NOTICE: CREATE TABLE will create implicit sequence refers_id_seq for
serial column refers.id
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index
refers_pkey for table refers
CREATE TABLE
Zeit: 63,477 ms
test=*# insert into refers (name, parent_id) values ('xxx',null);
INSERT 0 1
Zeit: 0,686 ms
I think, NULL is more sensible than inserting a 'random' value like 0.
Andreas
--
Really, I'm not out to destroy Microsoft. That will just be a completely
unintentional side effect. (Linus Torvalds)
If I was god, I would recompile penguin with --enable-fly. (unknown)
Kaufbach, Saxony, Germany, Europe. N 51.05082°, E 13.56889°
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
--
Red Leader 1 -- Out
URL: http://apps.facebook.com/faceblogged/?uid=674333666
Re: [GENERAL] Self-referential records
On Sunday 24. January 2010 16.22.00 Wayne E. Pfeffer wrote: If you do not use null to represent a root node, when you go to unwind the data from the table to generate a hierarchy tree, you could end up with an infinite loop. The query will always be looking for the next parent in the hierarchy. Meaning, you will want to find the parent of a node using the given parent_id, the query will find the parent of 1 to be 1, then it will look again for the parent of 1 it will find 1, etc. etc. ad nauseum. I enjoy using recursion as much as the next guy, but this could cause some serious issues with the PostgreSQL query engine eating up system resources. It doesn't really matter if the root node is NULL or 0 or whatever. You just have to realize that the root node is a special case and program accordingly. An adjacency tree is not a normalized structure, and will never be. It's the programmer's responsibility to ensure that circular references can't occur. regards, -- Leif Biberg Kristensen http://solumslekt.org/ -- 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] Referencing to system catalog problem
Thank you Adrian. I apparently missed that post. Guess I will have to come up with an different approach. Adrian Klaver adrian.kla...@gmail.com wrote in message news:201001231002.15874.adrian.kla...@gmail.com... On Saturday 23 January 2010 6:15:36 am Davor J. wrote: I am logged in as superuser. I am trying to create something similar to this: Code: CREATE TABLE tbl_unit_convfunctions( unit_from integer REFERENCES tbl_units (unit_id), unit_to integer REFERENCES tbl_units (unit_id), proc_id oid REFERENCES pg_proc (oid) )but no matter what I refer to from pg_proc, i get the error message: ERROR: permission denied: pg_proc is a system catalog SQL state: 42501 Has anyone any suggestions how to do something similar, or even better: how to solve this error. I couldn't find any useful information on the net about this issue. Thanks, Davor Original post: http://forums.devshed.com/postgresql-help-21/referencing-to-system-catalog- problem-670063.html Note: OID's are unique in that table, and should be referable, and I explicitely granted the REFERENCE priviledge to the superuser. You can't have FKs to system tables. See this post for explanation: http://archives.postgresql.org/pgsql-general/2004-12/msg00840.php -- Adrian Klaver adrian.kla...@gmail.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- 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] Recursion in triggers?
Gauthier, Dave wrote: What I need to know is if, in the “after” update trigger I make the subsequent updates to other records in the same table, with the OLD/NEW record ponters be set properly in those subsequent update trigger invocations? They'll be set properly. I'm not sure they'll be set how you want/expect, though. Will the current and modified NEW.* values be passed down into the next update trigger “before” call as OLD.* values? Eh? Why would they? Your AFTER trigger when fired modifies a different record, right, rather than modifying the same one again. So why would the `NEW' and `OLD' variables in the second firing have anything to do with those in the first firing? (Or have I misunderstood what you're trying to ask?) Is your question really one about when the changes become visible? Ie, if an AFTER UPDATE ... FOR EACH ROW trigger performs an UPDATE on a table, does the second instance of that same trigger procedure see the changes made to the first row? That I can't give you a guaranteed-accurate answer to right now (though I think so) ... but suggest that writing a simple test case would be a a pretty conclusive way to find out if the docs are unclear. -- Craig Ringer -- 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] [HACKERS] Sugerencia de opcion
Robert Haas robertmh...@gmail.com writes: 2009/1/22 Informatica-Cooperativa Cnel. Oviedo informat...@coopovie.com.py: SELECT id, sum(salario) as SumaSalario FROM salarios GROUP BY id HAVING SumaSalario500; I've wished for that syntax once or twice myself, but I'm assuming there's a reason we haven't implemented it? It's contrary to standard. There are some other reasons you can find in the archives, too. 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
Re: [GENERAL] How to use PG_DUMP?
Andre Lopes lopes80an...@gmail.com writes: I have generated the CREATE statements using a modeling tool, and I have created another database to test the changes. My problem is the order of the INSERT statements generated by PG_DUMP [-a -d], causing errors because of the order of the INSERTS. If you are talking about foreign key constraint failures, the best way is to use a regular dump (not -a). pg_dump will order the data and constraint creations properly then. 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
Re: [GENERAL] Recursion in triggers?
Ya, I worded the original poorley. Let me try again The after update trigger on the table sets some of the NEW.column values for record A. Then it executes another update on the same table, but on record B. That second execution of the update trigger needs to see the mods made to record A. One table being updated, calling more updates in the after trigger, needs to see the mods made in the previous update trigger executions. It's like a cascading operation, but on just one table. Sorry about the confussion, and thanks for the help. -Original Message- From: Craig Ringer [mailto:cr...@postnewspapers.com.au] Sent: Sunday, January 24, 2010 11:34 AM To: Gauthier, Dave Cc: pgsql-general@postgresql.org Subject: Re: [GENERAL] Recursion in triggers? Gauthier, Dave wrote: What I need to know is if, in the after update trigger I make the subsequent updates to other records in the same table, with the OLD/NEW record ponters be set properly in those subsequent update trigger invocations? They'll be set properly. I'm not sure they'll be set how you want/expect, though. Will the current and modified NEW.* values be passed down into the next update trigger before call as OLD.* values? Eh? Why would they? Your AFTER trigger when fired modifies a different record, right, rather than modifying the same one again. So why would the `NEW' and `OLD' variables in the second firing have anything to do with those in the first firing? (Or have I misunderstood what you're trying to ask?) Is your question really one about when the changes become visible? Ie, if an AFTER UPDATE ... FOR EACH ROW trigger performs an UPDATE on a table, does the second instance of that same trigger procedure see the changes made to the first row? That I can't give you a guaranteed-accurate answer to right now (though I think so) ... but suggest that writing a simple test case would be a a pretty conclusive way to find out if the docs are unclear. -- Craig Ringer -- 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] How to use PG_DUMP?
Hi, I have tested with pg_dump -u -p 5432 -d -f c:\test.sql mydatabase but the order of the INSERTS it is not the correct. What PG_DUMP does is to add the CONSTRAINTS after doing the INSERTS. There is a way to have the correct order of the INSERTS? I have tested the COPY but does not insert nothing. What can I do? Best Regards, On Sun, Jan 24, 2010 at 5:22 PM, Tom Lane t...@sss.pgh.pa.us wrote: Andre Lopes lopes80an...@gmail.com writes: I have generated the CREATE statements using a modeling tool, and I have created another database to test the changes. My problem is the order of the INSERT statements generated by PG_DUMP [-a -d], causing errors because of the order of the INSERTS. If you are talking about foreign key constraint failures, the best way is to use a regular dump (not -a). pg_dump will order the data and constraint creations properly then. regards, tom lane
[GENERAL] postgres
All how to get rid of the postmaster that is still running, but I deleted the $PGDATA cluster file, so it can not stop it. but I can not use the port again for new cluster. PLEASAE HELP. Amy
[GENERAL] port question
I have installed a v8.4 and first port using localhost is ok. but the second one using different port will get error when connect using IP address. Is that only one port is allowed for one server ? please help - need expert's advice. thanks Amy
Re: [GENERAL] port question
Amy Smith wrote: I have installed a v8.4 and first port using localhost is ok. but the second one using different port will get error when connect using IP address. Is that only one port is allowed for one server ? please help - need expert's advice. you can run different instances of postgresql on different ports as long as their $PGDATA directories are different. is this second server configured to LISTEN_ADDRESSES='*' ? -- 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] Recursion in triggers?
Gauthier, Dave dave.gauth...@intel.com writes: Ya, I worded the original poorley. Let me try again The after update trigger on the table sets some of the NEW.column values for record A. Then it executes another update on the same table, but on record B. That second execution of the update trigger needs to see the mods made to record A. Changing NEW in an after trigger has no effect outside the trigger function itself. It's too late to affect the data that went into the table --- that's more or less the whole point of AFTER vs BEFORE triggers. However, once you get that issue straightened out, it is true that triggers fired pursuant to the UPDATE inside the first trigger will also see whatever data changes the first trigger saw. See http://www.postgresql.org/docs/8.4/static/trigger-datachanges.html 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
Fwd: [GENERAL] [LibPQ] Trying PQconnectStart: it doesn't seem to connect
From: Alessandro Agosto the.6o...@gmail.com Date: 2010/1/24 Subject: Re: [GENERAL] [LibPQ] Trying PQconnectStart: it doesn't seem to connect To: Craig Ringer cr...@postnewspapers.com.au Hi, thank you for your reply. 2010/1/24 Craig Ringer cr...@postnewspapers.com.au What's wrong with psycopg2 for this purpose? Python's threading support (in the standard CPython interpreter) is awful due to the global interpreter lock. However, it works for waiting on blocking sockets as the GIL is released before entering most C-language routines. So you should be able to use psycopg2 in dedicated I/O worker threads just fine. If you're trying to use non-blocking sockets and select(...) with libpq, well, _then_ you'll have to go outside psycopg2. Be aware, though, that using SSL sockets in a non-blocking manner can be ... complicated ... so look into that in detail before deciding on this path. Multiple threads with blocking connections is likely to be a LOT easier. Yes, i know psycopg2 and i like it. But as you said i'm trying to use non-blocking socket and epoll (but writing a mock up i can use poll and work on events later). Honestly i'm not aware on every problem that async programming comport (like specific ssl problems). Anyway multiplexing programming is, yes complicated, but also powerful and a program that uses multiplexing has a lower footprint than thread/fork programs. In python, as you said there is the evil GIL. Frankly, though, you're never going to get wonderful results out of this. Twisted tries, but you've probably seen the issues it has working around the GIL and the limited success it has doing so. CPython's GIL dooms it to be a pretty shoddy language for high concurrency use, whether you use an async socket server model or a blocking threaded model. I know twisted but sincerely i don't like to write code that makes a lot of callbacks, but this is my personal opinion. I've used it a lot of times. Probably you are right but on network programming async servers are faster than classic threaded versions, also if mean more problems. The lack of an asynchronous database interface is a problem that i could solve using more processes of my webserver but this isn't a real solution. So i'm on this project. Can someone tell me what i wrong? And if you know some resource that explains with an example how i should to verify the connection during the select/poll, would be appreciated. You can't reliably verify that a connection is alive with select/poll. TCP/IP timeouts are very long and until the connection times out at the TCP/IP level, it might appear fine even though the peer died hours ago. I'm not yet within select/poll cycle, this is the first call that should return CONNECTION_OK or CONNECTION_BAD (refering to docs). Probably i wrong but this call seems to return another status, or as you said i cannot know (also if i'm out of any cycle) if the connection is ok or not. If the async apis became a problem , can i call the *synchronous* apis through my webserver's main event loop? This should work or not? (i mean to make the connection asynchronous). Thank you again, Greetings. -- Alessandro A. -- Alessandro A.
Re: Fwd: [GENERAL] [LibPQ] Trying PQconnectStart: it doesn't seem to connect
Alessandro Agosto wrote:
I'm not yet within select/poll cycle, this is the first call that should
return CONNECTION_OK or CONNECTION_BAD (refering to docs).
That would be the behavior of PQconnectdb(), not PQconnectStart().
Have you read that part of the doc:
quote
Other states might also occur during (and only during) an asynchronous
connection procedure. These indicate the current stage of the connection
procedure and might be useful to provide feedback to the user for example.
These statuses are:
CONNECTION_STARTED
Waiting for connection to be made.
CONNECTION_MADE
Connection OK; waiting to send.
CONNECTION_AWAITING_RESPONSE
Waiting for a response from the server.
CONNECTION_AUTH_OK
Received authentication; waiting for backend start-up to finish.
CONNECTION_SSL_STARTUP
Negotiating SSL encryption.
CONNECTION_SETENV
Negotiating environment-driven parameter settings.
Note that, although these constants will remain (in order to maintain
compatibility), an application should never rely upon these occurring in a
particular order, or at all, or on the status always being one of these
documented values. An application might do something like this:
switch(PQstatus(conn))
{
case CONNECTION_STARTED:
feedback = Connecting...;
break;
case CONNECTION_MADE:
feedback = Connected to server...;
break;
--
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] \dt+ sizes don't include TOAST data
Greg Smith g...@2ndquadrant.com writes: Florian Weimer wrote: The sizes displayed by \dt+ in version 8.4.2 do not take TOAST tables into account, presumably because the pg_relation_size does not reflect that, either. I think this is a bit surprising. From a user perspective, these are part of the table storage (I understand that the indices might be a different story, but TOAST table are a fairly deep implementation detail and should perhaps be hidden here). As of last week there's a new pg_table_size available that does what you want here: http://archives.postgresql.org/pgsql-committers/2010-01/msg00288.php I don't believe \dt+ has been updated yet to use that though; that's worth considering for a minute, not sure anybody thought about it yet. We could only use pg_table_size against a backend = 9.0, which would mean that the displayed results mean something different depending on which backend version psql is being used with. That's not necessarily a deal-breaker, but it does seem a bit evil. An alternative worth thinking about is to make it use pg_total_relation_size instead of pg_relation_size. That's available, with similar semantics, in all versions that have pg_relation_size either (ie, = 8.1). Also, this is arguably more nearly the right thing since at the level of \dt+ I think people would expect indexes to get folded in too. 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
Re: [GENERAL] \dt+ sizes don't include TOAST data
Tom Lane wrote: Greg Smith g...@2ndquadrant.com writes: Florian Weimer wrote: The sizes displayed by \dt+ in version 8.4.2 do not take TOAST tables into account, presumably because the pg_relation_size does not reflect that, either. I think this is a bit surprising. From a user perspective, these are part of the table storage (I understand that the indices might be a different story, but TOAST table are a fairly deep implementation detail and should perhaps be hidden here). As of last week there's a new pg_table_size available that does what you want here: http://archives.postgresql.org/pgsql-committers/2010-01/msg00288.php I don't believe \dt+ has been updated yet to use that though; that's worth considering for a minute, not sure anybody thought about it yet. We could only use pg_table_size against a backend = 9.0, which would mean that the displayed results mean something different depending on which backend version psql is being used with. That's not necessarily a deal-breaker, but it does seem a bit evil. Perhaps we can emulate pg_table_size on earlier server versions, using a query which provides the sum of table plus toast items. It would be a bit slower, but the normal case of using the same server version would be fast. -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] 100% of CPU utilization postgres process
Hello, I observed the event that CPU utilization of the process related to postgres records almost 100% for unknown reason. It would be appreciated if any of you provide any information on this. The following line is a part of the result of ps -auxeww. = pgsql 682 99.0 0.1 9336 2740 ?? Rs 27Nov08 343573:19.27 USER=pgsql MAIL=/var/mail/pgsql HOME=/usr/local/pgsql BLOCKSIZE=K PGLOCALEDIR=/usr/local/share/locale PGSYSCONFDIR=/usr/local/etc/postgresql PATH=/sbin:/bin:/usr/sbin:/usr/bin:/usr/games:/usr/local/sbin:/usr/local/bin:/usr/local/pgsql/bin SHELL=/bin/sh PWD=/usr/local/pgsql FTP_PASSIVE_MODE=YES PGDATA=/sa/db postgres: stats collector process(postgres) = Judging from the result, I could see that stats collector process caused this unusually high CPU utilization rate. I found similar problem at http://archives.postgresql.org/pgsql-general/2008-06/msg00934.php, although there seemed no clear cause proven nor the statement that it's because of postgres bug. Also, the following message was seen in the postgres log. == Nov 12 02:49:53 postgres[681]: [2-1] WARNING: worker took too long to start; cancelled Nov 12 02:50:53 postgres[681]: [3-1] WARNING: worker took too long to start; cancelled … Nov 12 11:14:12 postgres[681]: [506-1] WARNING: worker took too long to start; cancelled Nov 12 11:15:12 postgres[681]: [507-1] WARNING: worker took too long to start; cancelled == Once the message which started with postgres[xxx] appeared, it had been repeated until the OS was manually shut down. I'm not sure if each of the two could happen separately nor if one of the two could trigger the other. Do any of you happen to know more than what was posted at http://archives.postgresql.org/pgsql-general/2008-06/msg00934.php ? The event above was observed under the condition as follows. -Postgres version : PostgreSQL 8.3.3 on i386-portbld-freebsd7.0, compiled by GCC cc (GCC) 4.2.1 20070719 [FreeBSD] (It was installed via ports.) -To connect the PostgreSQL Database, the following drivers are used. php5-pdo_pgsql-5.2.12 p5-DBD-Pg-2.16.0 -Operating system and version ・FreeBSD 7.0-RELEASE-p2 -Hardware ・CPU : Intel, Xeon2.4 ・RAM : 2GB RAM ・Storage RAID controller : LSI MegaRAID -battery backed cache : none -write-back : disabled -Software RAID : not used -SAN : not used -disk configuration : 3 HITACHI 7,200rpm SATA disks in RAID5 - filesystem : ufs Regards, _ 【節約！】インターネット代、見直しませんか？ http://campaign.live.jp/eaccess/Top/
Re: [GENERAL] 100% of CPU utilization postgres process
Hashimoto Yuya wrote: -Postgres version : PostgreSQL 8.3.3 on i386-portbld-freebsd7.0, compiled by GCC cc (GCC) 4.2.1 20070719 [FreeBSD] 8.3.3 is fairly old, they are up to 8.3.9 in that version. seee the release notes for each version from 8.3.4 to 8.3.9 to see what bugs were fixed... http://www.postgresql.org/docs/current/static/release.html -disk configuration : 3 HITACHI 7,200rpm SATA disks in RAID5 raid 5 performs rather badly on database update kind of operations, especially without a battery backed writeback cache not saying this is your problem, but its not a real good idea. We use raid1+0 aka raid10 for our database volumes -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Postgresql HA on MSCS over VMWARE
As title, please help. I want to setup Postgresql HA by MSCS in VMWARE platform. (win server 2003, PG 8.3 on 32 bit) MSCS has been setup, the problem can't start postgresql service. PGDATA is on the shared disk. I tried generic service, and application, either one won't bring up postgresql database engine service. did I do something wrong? Thanks a bunch.
Re: [GENERAL] 100% of CPU utilization postgres process
Hashimoto Yuya hill_cl...@hotmail.com writes: [ lots of time spent by stats collector process ] How large is $PGDATA/global/pgstat.stat ? If it's very large (many MB), try doing pg_stats_reset(). If that makes the stats collector CPU usage drop, consider an update to PG 8.4.x, which is more efficient at dealing with large stats files. 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
Re: [GENERAL] 100% of CPU utilization postgres process
8.3.3 is fairly old, they are up to 8.3.9 in that version. seee the release notes for each version from 8.3.4 to 8.3.9 to see what bugs were fixed... http://www.postgresql.org/docs/current/static/release.html Thanks, I was planning to update the postgres to the newer version, but I was not sure if the problem would be solved or not since I couldn't find statement about stats collector ,as far as I read through the release notes for each version from 8.3.4 to 8.3.9. That's way I posted a question, hoping I could get any information... Regards, _ Windows 7とOfficeが安くなる！（ダウンロード版） http://promotion.live.jp/special/msstore/
Re: [GENERAL] 100% of CPU utilization postgres process
Thanks, How large is $PGDATA/global/pgstat.stat ? Unfortunately, the size of pgstat.stat was not taken when the CPU utilization of the postgress process reached nearly 100%... If it's very large (many MB), try doing pg_stats_reset(). If that makes the stats collector CPU usage drop, consider an update to PG 8.4.x, which is more efficient at dealing with large stats files. Does it mean that CPU usage rate of stats collector process could possiblly reach 100% unless the postgres is updated to the version 8.4.x, instead of 8.3.x? (So far I'm planning to update the postgres to the version 8.3.9) Regards, _ 【節約！】インターネット代、見直しませんか？ http://campaign.live.jp/eaccess/Top/
[GENERAL] Updates: all or partial records
Scenario: You have to update a record. One or more fields are unchanged from the original record being altered. So you have two options: 1) Include those fields in your UPDATE statement, even though they are unchanged; 2) Omit unchanged fields from the UPDATE statement. My first inclination is to omit unchanged fields. However, I have the idea that PG simply marks the existing record to be dropped, and generates a whole new row by copying unspecified fields from the original record. My question is, which is more efficient? Performance-wise, does it matter whether unchanged fields are included or omitted on UPDATE statements? Paul -- Paul M. Foster -- 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] Variadic polymorpic functions
2010/1/23 Pavel Stehule pavel.steh...@gmail.com: 2010/1/22 Vincenzo Romano vincenzo.rom...@notorand.it: 2010/1/22 Tom Lane t...@sss.pgh.pa.us: Vincenzo Romano vincenzo.rom...@notorand.it writes: 2010/1/22 Tom Lane t...@sss.pgh.pa.us: regression=# CREATE FUNCTION q( fmt text, variadic args any ) And this would allow for a stdarg-like argument list? Yeah, it should work, given suitable C code. Great! I wrote this function year ago. look on content http://pgfoundry.org/projects/pstcollection/ Pavel, that format() function should be included into official contribs. What about HOWTO compile? -- 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] Postgresql HA on MSCS over VMWARE
On Monday, January 25, 2010, Steeles stee...@gmail.com wrote: As title, please help. I want to setup Postgresql HA by MSCS in VMWARE platform. (win server 2003, PG 8.3 on 32 bit) MSCS has been setup, the problem can't start postgresql service. PGDATA is on the shared disk. I tried generic service, and application, either one won't bring up postgresql database engine service. did I do something wrong? That should work. My guess is it's a permissions issue. Check what you get in your logs - both in the pg_log directory and in the windows event log. -- 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] Updates: all or partial records
Paul M Foster wrote: Scenario: You have to update a record. One or more fields are unchanged from the original record being altered. So you have two options: 1) Include those fields in your UPDATE statement, even though they are unchanged; 2) Omit unchanged fields from the UPDATE statement. My first inclination is to omit unchanged fields. However, I have the idea that PG simply marks the existing record to be dropped, and generates a whole new row by copying unspecified fields from the original record. My question is, which is more efficient? Performance-wise, does it matter whether unchanged fields are included or omitted on UPDATE statements my first order guess is, sending and having to parse the additional unchanged fields in your UPDATE statement is more expensive than letting the engine just copy them from the old tuple to the new. -- 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] Variadic polymorpic functions
2010/1/25 Vincenzo Romano vincenzo.rom...@notorand.it: 2010/1/23 Pavel Stehule pavel.steh...@gmail.com: 2010/1/22 Vincenzo Romano vincenzo.rom...@notorand.it: 2010/1/22 Tom Lane t...@sss.pgh.pa.us: Vincenzo Romano vincenzo.rom...@notorand.it writes: 2010/1/22 Tom Lane t...@sss.pgh.pa.us: regression=# CREATE FUNCTION q( fmt text, variadic args any ) And this would allow for a stdarg-like argument list? Yeah, it should work, given suitable C code. Great! I wrote this function year ago. look on content http://pgfoundry.org/projects/pstcollection/ Pavel, that format() function should be included into official contribs. What about HOWTO compile? There are not consensus about final semantic - some people prefer sprintf like, some others PostgreSQL RAISE NOTICE like. so I'll keep it outside. I looking on source of pstcollection - missing documentation, missing regress test. I never rebuild it outside PostgreSQL source tree - so it could be problem. Now: copy src to contrib directory, make, make install - like standard contrib module. If you would to add some doc or notes, please, add it. Pavel -- 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] Variadic polymorpic functions
2010/1/25 Pavel Stehule pavel.steh...@gmail.com: 2010/1/25 Vincenzo Romano vincenzo.rom...@notorand.it: 2010/1/23 Pavel Stehule pavel.steh...@gmail.com: 2010/1/22 Vincenzo Romano vincenzo.rom...@notorand.it: 2010/1/22 Tom Lane t...@sss.pgh.pa.us: Vincenzo Romano vincenzo.rom...@notorand.it writes: 2010/1/22 Tom Lane t...@sss.pgh.pa.us: regression=# CREATE FUNCTION q( fmt text, variadic args any ) And this would allow for a stdarg-like argument list? Yeah, it should work, given suitable C code. Great! I wrote this function year ago. look on content http://pgfoundry.org/projects/pstcollection/ Pavel, that format() function should be included into official contribs. What about HOWTO compile? There are not consensus about final semantic - some people prefer sprintf like, some others PostgreSQL RAISE NOTICE like. so I'll keep it outside. I looking on source of pstcollection - missing documentation, missing regress test. I never rebuild it outside PostgreSQL source tree - so it could be problem. Now: copy src to contrib directory, make, make install - like standard contrib module. If you would to add some doc or notes, please, add it. I figured that out (from the Makefile) USE_PGXS=1 make install -- 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] Variadic polymorpic functions
2010/1/25 Vincenzo Romano vincenzo.rom...@notorand.it: 2010/1/25 Pavel Stehule pavel.steh...@gmail.com: 2010/1/25 Vincenzo Romano vincenzo.rom...@notorand.it: 2010/1/23 Pavel Stehule pavel.steh...@gmail.com: 2010/1/22 Vincenzo Romano vincenzo.rom...@notorand.it: 2010/1/22 Tom Lane t...@sss.pgh.pa.us: Vincenzo Romano vincenzo.rom...@notorand.it writes: 2010/1/22 Tom Lane t...@sss.pgh.pa.us: regression=# CREATE FUNCTION q( fmt text, variadic args any ) And this would allow for a stdarg-like argument list? Yeah, it should work, given suitable C code. Great! I wrote this function year ago. look on content http://pgfoundry.org/projects/pstcollection/ Pavel, that format() function should be included into official contribs. What about HOWTO compile? There are not consensus about final semantic - some people prefer sprintf like, some others PostgreSQL RAISE NOTICE like. so I'll keep it outside. I looking on source of pstcollection - missing documentation, missing regress test. I never rebuild it outside PostgreSQL source tree - so it could be problem. Now: copy src to contrib directory, make, make install - like standard contrib module. If you would to add some doc or notes, please, add it. I figured that out (from the Makefile) USE_PGXS=1 make install ok, if you would, add account on pgfoundry I'll add you to developer group for pstcollection Pavel -- 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
