Re: [GENERAL] empty string causes planner to avoid index. Makes me sad.
On Fri, 27 Nov 2009, Jeff Amiel wrote: --- On Fri, 11/27/09, Tom Lane t...@sss.pgh.pa.us wrote: You didn't show us any evidence of that, either.? Both of your test cases are using the index. Ok...third try. The cost when passing in an empty string is SIGNIFICANTLY higher than when not. Wouldn't seem that the planner is using the index effectively. But it's also estimating that it's aggregating over around 1 times as many rows presumably because it thinks empty string is alot more common. That might not be the case in the actual data, but the estimated difference is the likely cause of the plan differences. What are the actual runtimes and rowcounts for the queries with different values you're trying? Explain analyze output would be useful for that. -- 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] What order of steps of the postgres when you change information in the table?
On Sat, 31 Oct 2009, Denis Feklushkin wrote: Problem: It is necessary to synchronize the users table with an external storage of passwords (krb5) I made a trigger: CREATE TRIGGER 10_krb5 AFTER INSERT OR UPDATE OR DELETE ON users FOR EACH ROW EXECUTE PROCEDURE user2krb5_python(); Everything works, except that when you delete a row from table users foreign keys checking occurs after this trigger. And in case of any problems with the referencing record is an exception, rollback is occured (this is ok), but the trigger user2krb5_python() was executed and the user from the external storage removed. Checking of foreign keys occurs after the AFTER-trigger is ok? Check of the primary key, unique, and other constraints occurs in the very beginning, I checked. If I remember correctly you're allowed to put an after trigger before or after the constraint check for foreign keys based on the naming of the trigger as the key is checked in a trigger. IIRC, with a name like 10_... it will compare lower so happens before the check and a name like krb5 it would come after. However, I don't think you can currently have both the property that you will never have a failure to commit after your external action runs and that your external action can abort the transaction if the external action fails. -- 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] interface for non-SQL people
On Fri, 9 Oct 2009, Joshua D. Drake wrote: On Thu, 2009-10-08 at 19:16 -0600, Scott Marlowe wrote: On Thu, Oct 8, 2009 at 2:22 PM, Merlin Moncure mmonc...@gmail.com wrote: The #1 tool you have at your disposal is the human brain. I personally think GUI database tools are counter productive and huge time wasters. SQL requires lateral thinking but once you have your head around how joins work and the general syntax of queries you should have no problem getting data out of your database. SQL is a 'man machine interface' :-). It's a very high level language with a lot of power. The gui 'wrappers' that I've seen actually obfuscate the concepts. Amen to that. I'd rather spend a little bit of my time each week going over correlated subqueries with a user than trying to get good performance on a reporting server that's hammered by bad queries. Which is what a lot of query builders basically do. Good lord people. Not be helpful much? [...] JD... Who sits in bewilderment I'm fairly bewildered as well. I mean, why would someone who is emailing with an address from a company that presumably should care about how it looks on the mailing list bother to prefix his answer to a question with what amounts to an attack on other people in the thread. It'd be a bit odd, but understandable if the message was an attack only on the answers, but is just baffling when it effectively includes attacks on the people. -- 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] bytea question
On Mon, 28 Sep 2009, Maximilian Tyrtania wrote: testdb=# create table byteatest(blob bytea); CREATE TABLE testdb=# insert into byteatest (blob) values (E'\\007'); INSERT 0 1 testdb=# insert into byteatest (blob) values (E'\\008'); ERROR: invalid input syntax for type bytea LINE 1: insert into byteatest (blob) values (E'\\008'); Or also: testdb=# SELECT E'\\001'::bytea; bytea --- \001 (1 row) testdb=# SELECT E'\\008'::bytea; ERROR: invalid input syntax for type bytea LINE 1: SELECT E'\\008'::bytea; As far as I can see i followed the escaping rules given in http://www.postgresql.org/docs/current/static/datatype-binary.html From that: When entering bytea values, octets of certain values must be escaped (but all octet values can be escaped) when used as part of a string literal in an SQL statement. In general, to escape an octet, convert it into its three-digit octal value and precede it by two backslashes. 008 isn't a valid octal value, you'd want 010 to represent 8. -- 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] Foreign Key Deferrable Misunderstanding or Bug?
On Thu, 6 Aug 2009, Paul Rogers wrote: Why does the attached script fail with a foreign key constraint violation? Referential actions are not deferred when a constraint is marked deferrable (as that appears to be what the spec wants), so ON DELETE RESTRICT will still fail on the statement, while ON DELETE NO ACTION (ie, only check at constraint check time) should wait to the end. -- 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] column name gets substitudes inside an execute statement of a trigger function. how to avoid it?!
On Tue, 9 Jun 2009, G. Allegri wrote:
Hello list.
I'm a newbie with plpgsql, so I'm sorry for doing stupid questions...
I have a situation whit one table where items are related to two other
tables through a common id (unique in the first table) and the table
name. Whenever the user execute an operation on an item of the first
one (prima), the related items in tables seconda or terza must
be updated.
CREATE TABLE prima
(
id serial NOT NULL,
nome character varying(100),
table character varying(10), ## this contains seconda or terza
CONSTRAINT prima_pkey PRIMARY KEY (id)
)
CREATE TABLE seconda
(
id serial NOT NULL,
nome character varying(100),
CONSTRAINT seconda_pkey PRIMARY KEY (id)
)
CREATE TABLE seconda
(
id serial NOT NULL,
nome character varying(100),
CONSTRAINT seconda_pkey PRIMARY KEY (id)
)
So I need to retrieve the table name dynamically inside the function,
and AFAIK I can do it only using an execute statement. BUT when I do
something like INSERT INTO prima (nome,table) VALUES
('lets_try','seconda') I get the following error:
ERROR: Column 'lets_try' does not exist
LINE 1: INSERT INTO seconda (nome) VALUES ('lets_try')
Are you sure that's the error message (specifically the context)?
Specifically, the query below in the function looks like it would
generate:
INSERT INTO seconda (name) VALUES (lets_try)
which means use lets_try as a quoted column name, as opposed to
('lets_try') which means the string literal.
In addition, what are the semantics of update supposed to be? It looks
like if you update a row in prima, it's going to set all the name fields
to the new name? Is that intentional, or was the intent to change only the
row with the old name?
CREATE OR REPLACE FUNCTION fun1() RETURNS TRIGGER AS
$primaprova$
DECLARE
nome varchar;
BEGIN
IF (TG_OP='INSERT') THEN
execute 'INSERT INTO ' || NEW.table || ' (nome) VALUES( ' || NEW.nome
|| ' );';
RETURN NEW;
ELSEIF (TG_OP='UPDATE') THEN
execute 'UPDATE '|| NEW.table ||' SET nome='|| NEW.nome ||';';
RETURN NEW;
ELSEIF (TG_OP='DELETE') THEN
execute 'DELETE FROM '|| OLD.table ||' where nome='|| OLD.nome ||';';
RETURN OLD;
END IF;
END;
$primaprova$ LANGUAGE plpgsql;
CREATE TRIGGER tr1 AFTER INSERT OR UPDATE OR DELETE ON prima FOR EACH
ROW EXECUTE PROCEDURE fun1();
--
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] Problem defining deferred check constraints
On Sun, 25 Jan 2009, Thomas Kellerer wrote: Hi, I'm playing around with deferred constraints and according to the manual, it should be possible to declare a check constraint as deferred. At least that's how I read the definition of /column_constraint/ at http://www.postgresql.org/docs/8.3/static/sql-createtable.html In the full description in that page for deferrable/not deferrable, it also states: Only foreign key constraints currently accept this clause. Currently, you'd probably need to use a constraint trigger to check the constraint to get similar functionality. -- 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] Question about NOT NULL and default values.
On Fri, 17 Oct 2008, Tim Uckun wrote: Is there a way to change this behavior so that an attempt to set the column to NULL will result in the default value being put in the field? I don't think so specifically with default, but you could use a before trigger instead that would put in a value in the new row if NULL was given. -- 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] Question about NOT NULL and default values.
On Thu, 16 Oct 2008, Scott Marlowe wrote: On Thu, Oct 16, 2008 at 10:01 PM, Stephan Szabo [EMAIL PROTECTED] wrote: On Fri, 17 Oct 2008, Tim Uckun wrote: Is there a way to change this behavior so that an attempt to set the column to NULL will result in the default value being put in the field? I don't think so specifically with default, but you could use a before trigger instead that would put in a value in the new row if NULL was given. I'm pretty sure that will fail as the primary key or not null constraint comes first. Well, since he said that he'd removed the not null constraint in his testing, I figured that was a viable option. -- 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] Fwd: Set-valued function in wrong context
On Thu, 9 Oct 2008, Raymond O'Donnell wrote:
gfc_bookings=# select * from make_time_series('11:00', '14:00', 30);
ERROR: set-valued function called in context that cannot accept a set
CONTEXT: PL/pgSQL function make_time_series line 10 at for over
select rows
Now, I know what the error means, and I reckon it's because of the
cast(), but for the life of me I can't see what to do about it. Any help
will be appreciated...
for ATime in
select start_time + s.a
from cast(generate_series(0, TotalMins, mins_delta) || ' minutes' as
interval) as s(a)
I think you'd end up wanting something like:
FROM ( select a * interval '1 minute' from generate_series(0, TotalMins,
mins_delta) as s(a) ) as s(a)
I changed the concatenation and cast into an interval multiply, but you
could easily do things the other way as well.
--
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] subquery in FROM must have an alias
On Sun, 28 Sep 2008, Ashutosh Chauhan wrote: Hi all, This has been asked before and answered as well. http://archives.postgresql.org/pgsql-sql/2007-12/msg2.php but I still cant figure out why postgres throws this error message even when I have provided the aliases. My query: select a,b from (billing.item JOIN ( select * from ( billing.invoice JOIN billing.customer on (id_customer_shipped = customer_uid and address = 'pgh' )) as temp2 )) as temp; I have two from clauses so I have provided two corresponding alias names for those two from clauses. If you break the above down a bit, you have: select a,b from ( billing.item join (select * from ( billing.invoice join billing.customer on (id_customer_shipped = customer_uid and address='pgh') ) as temp2 ) ) as temp; What the system is complaining about is the subselect (select * from ... ) not having an alias. You've aliased the billing.invoice join billing.customer one and (billing.item join (...)) one, but not the subselect. In fact, I believe the two aliases you're using aren't strictly necessary. Also, the above appears to be missing the condition for the outermost join. Maybe something like the following will work with a filled in on condition: select a,b from ( billing.item join (select * from ( billing.invoice join billing.customer on (id_customer_shipped = customer_uid and address='pgh') ) ) as temp on (...) ) -- 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] PL/PGSQL - character varying as function argument
On Fri, 26 Sep 2008, Chris Baechle wrote: When I try to run it with: select user_checkCredentials(asdf); Actually, I think the function probably isn't at fault here, string literals should be surrounded with ' not . -- 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] Help with a foreign key with non-unique reference?
On Tue, 16 Sep 2008, Brent Wood wrote: I need a foreign key (or equivalent) where the referenced table cannot have a unique constraint. Well, do you need a full foreign key or just the insert-time check on the referencing table? Does the referenced table get updates or deletes that you want to watch for, and do you want those to error or to do the equivalent of one of the referential actions? For the insert-time check only if you don't care about deletes or updates to the referenced table, a trigger that checks for existance is probably good enough. -- 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] Problem with trigger function
On Wed, 3 Sep 2008, Mira Dimitrijevic wrote:
Hi,
I wrote the trigger function below and when trying to execute it, I
get the following error:
15:00:42 [CREATE - 0 row(s), 0.000 secs] [Error Code: 0, SQL
State: 42601] ERROR: syntax error at or near INSERT
I am using DBVisualizer's SQL Commander window, not the create
funciton option. I know the SQL is correct - I can execute it just
by itself. The problem is somewhere in the
syntax for a postgres trigger function. Any input on what the problem
might be would be just fabulous!
It looks like you're not always quoting properly if the below is an exact
quote. For example you double the single quotes in the IF tg_op =
''INSERT'' but don't double them in the VALUES ('INSERT').
You might find it easier to use dollar quoting for the function body
(using $$ instead of ' to wrap the body) if you're using a version
that supports it which will mean you shouldn't need to double any of the
quotes inside the function.
CREATE OR REPLACE FUNCTION audit_sequence_update() RETURNS trigger AS '
BEGIN
IF tg_op = ''INSERT'' THEN
INSERT INTO audit_sequence_update(operation, day,
owner_uid, sequence)
VALUES
('INSERT', NEW.day, NEW.owner_uid, NEW.sequence);
ELSE
INSERT INTO audit_sequence_update(operation, day,
owner_uid, sequence)
SELECT
'UPDATE', day, owner_uid, sequence
FROM sequence where isdid=NEW.isdid;
END IF;
RETURN NEW;
END
' LANGUAGE plpgsql;
--
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] deleting the master but not the detail
On Thu, 17 Jul 2008, Ismael wrote: So is there no other way to do it but to verify the integrity using triggers and drop the referential constraints? Well, you could do something using a before delete trigger on the referencing table that returns NULL to avoid the delete as well, but making it only prevent the deletions caused by the referential constraints might be difficult. -- 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] Exception handling
On Wed, 4 Jun 2008, sam wrote: Can someone explain me about the exception handling in postgresql. Iam not understanding the scope of a exception block. The exact confusion that iam facing is as follows: I have a procedure as follows BEGIN EXECUTE an update statement EXECUTE an insert statement EXCEPTION WHEN integrity_conatraint_violation THEN RAISE NOTICE END; This procedure is called within another procedure. Now when an exception is caught the updates are also getting rolled back. I need the exception block to work only for the insert statement. But if i dont write the exception block the whole program stops. Any suggestions? I think you're looking for something like: BEGIN EXECUTE an update BEGIN EXECUTE an insert EXCEPTION WHEN ... RAISE NOTICE ... END; END; The exception when is effectively associated with the block. -- 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] Script errors on run
On Wed, 4 Jun 2008, Ralph Smith wrote: -- == good_date := to_date(year||'-'||month||'-'||day , '-MM-DD') ; RAISE NOTICE 'good_date = %',good_date ; Usecs := EXTRACT(EPOCH FROM TIMESTAMP good_date) ; END ; QUERY: SELECT EXTRACT(EPOCH FROM TIMESTAMP $1 ) CONTEXT: SQL statement in PL/PgSQL function usecs_from_date near line 92 Is this not a programmable extraction??? I'm missing something here. TIMESTAMP '...' describes a timestamp literal. If you wanted to explicitly cast the value in good_date as a timestamp, you'd probably want CAST(good_date AS TIMESTAMP). If good_date is of type date, however, I believe the cast to timestamp is implicit, so you should probably be able to just use extract(epoch from good_date). -- 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] Script errors on run
On Wed, 4 Jun 2008, Ralph Smith wrote: date_string := to_date(year||'-'||month||'-'||day , '-MM-DD') ; RAISE INFO 'date_string = %', date_string ; good_date := to_timestamp(date_string, '-MM-DD') ; RAISE INFO 'good_date = %', good_date ; This seems like alot of extra work, due to the implicit cast from date to timestamp. I think good_date := to_date(year || '-' || month || '-' || day, '-MM-DD') might work and just be simpler. UsecsD := EXTRACT(EPOCH FROM TIMESTAMP 'good_date') ; If good_date's already a timestamp, I think this should just be: EXTRACT(EPOCH FROM good_date) -- 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] Script errors on run
On Wed, 4 Jun 2008, Ralph Smith wrote: -- == good_date := to_date(year||'-'||month||'-'||day , '-MM-DD') ; RAISE INFO 'good_date = %', good_date ; UsecsD := EXTRACT(EPOCH FROM DATE 'good_date') ; You want something like: UsecsD := EXTRACT(EPOCH FROM good_date); Note the lack of single quotes. You want to use the variable's value, not a literal string with the value 'good_date'. -- 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] join ... using ... and - is this expected behaviour?
On Tue, 3 Jun 2008, Rob Johnston wrote: Just wondering if this is expected behaviour. When executing a query in the form of: select column from table join table using (column) and column = clause pgsql (8.2) returns the following: syntax error at or near and Obviously, you can get around this by using where instead of and, but shouldn't the format as used above be valid? No. The following is... select column from table join table on (column = column) and column = clause Yes. USING takes a column list, ON takes an expression as a search condition (and note from the syntax section that the parens are not always required around the expression). (t1.col1 = t2.col1) AND col3 = foo is still a valid search condition, but (col1) AND col3 = foo isn't a valid column list. The documentation indicates that the two formats of the query are equivalent (http://www.postgresql.org/docs/8.2/interactive/queries-table-expressions.html#QUERIES-JOIN) It's talking about the execution, not the syntax, but that could probably be more clear. -- 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] HELP with a query with blank fields
On Tue, 27 May 2008, J. Manuel Velasco wrote: Hello, This is the current query I have: SELECT dominis.nom, dominis.extensio, dominis.creat, dominis.expira, titulars.first_name, titulars.last_name, contactes_admin_tec.first_name, contactes_admin_tec.last_name, dns1.nom, dns2.nom, dominis.redirec, contactes_fac.nom, grups.nom FROM dominis, contactes_fac, dns as dns1, dns as dns2, titulars, contactes_admin_tec, grups WHERE dominis.id_c_f=contactes_fac.id AND dominis.id_dns1=dns1.id AND dominis.id_dns2=dns2.id AND dominis.id_titular=titulars.id AND dominis.id_c_a=contactes_admin_tec.id AND contactes_fac.id_grup=grups.id AND dominis.id_c_f = 724 The problem is that are registers that has not dominis.id_dns2 value and then they are not extracted. I need to show also these ones. I try playing with inner join, left join,... but I get this error: ERROR: referencia invalidad a una entrada de clausula FROM para la tabla dominis LINE 9: ON dominis.id_dns2 = dns2.id ^ HINT: Hay una entrada para la tabla dominis, pero este no puede ser referenciado desde esta parte de la consulta. Free translation: Invalid reference in FROM clausule. There is an entry in table dominis but it can't referenced from this part of the query. This usually means that you've mixed up the conversion to SQL join syntax. Since you aren't showing the exact query after conversion, it's hard to say exactly, but usually this comes up if you do something like FROM a, b LEFT JOIN c ON (a.col = c.col) because that's effectively a cross join (b left join c on (a.col = c.col)) not (a cross join b) left join c on (a.col = c.col) So, if you haven't converted entirely from comma separated from entries to SQL join syntax, you might want to try that first. So, something like: SELECT dominis.nom, dominis.extensio, dominis.creat, dominis.expira, titulars.first_name, titulars.last_name, contactes_admin_tec.first_name, contactes_admin_tec.last_name, dns1.nom, dns2.nom, dominis.redirec, contactes_fac.nom, grups.nom FROM dominis JOIN contactes_fac ON (dominis.id_c_f = contactes_fac.id) JOIN titulars ON (dominis.id_titular = titulars.id) JOIN contactes_admin_tec ON (dominis.id_c_a = contactes_admin_tec.id) JOIN grups ON (contactes_fac.id_grup = grups.id) JOIN dns as dns1 ON (dominis.id_dns1 = dns1.id) LEFT JOIN dns as dns2 ON (dominis.id_dns2 = dns2.id) WHERE dominis.id_c_f = 724 -- 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] ranked subqueries vs distinct question
On Wed, 14 May 2008, Karsten Hilbert wrote: Modifying to: select * from ( select distinct on (name) * from ( select *, 1 as rank from dem.urb where name ilike 'Lei%' and zip = '04317' union all -- avoid distinctness at this level select *, 2 as rank from dem.urb where name ilike 'Lei%' ) as inner_union ) as unique_union order by rank, name; This works. However, one nuisance remains: Because the distinct happens before the order by rank it is happenstance whether rank 1 cities (with zip) will be listed on top anymore. Can't you just do something like order by name, rank as part of the distinct on subselect to force it to pick the rank 1 row for a given name? So, basically select * from ( select distinct on ... order by name, rank ) order by rank, name; -- 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] String Comparison and NULL
On Mon, 28 Apr 2008 [EMAIL PROTECTED] wrote: I'm fairly new to PG and databases in general so this may very well be a problem in my thought process. If I have a simple table with an ID (integer) and Animal (text) like this... 1 Dog 2 Cat 3 NULL 4 Horse 5 Pig 6 Cat 7 Cat ... and I do something like select id where animal 'Cat'; then shouldn't 1, 3, 4 and 5 be picked? Comparisons against null with =, and so on return unknown not true or false and WHERE clauses only return rows where the condition is true. You might want to read up on the ternary (three valued) logic and nulls. I haven't read through it but the wikipedia page on null is pretty long. http://en.wikipedia.org/wiki/Null_%28SQL%29 -- 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] Updating with a subselect
On Tue, 22 Apr 2008, Leandro Casadei wrote: Hi, I need to update a field from a table based in a count. This is the query: updateshops setitemsqty = ( select count(*) from items i1 join shops s1 on i1.shopid = s1.shopid where s1.shopid = s0.shopid ) from shops s0 I think you'll actually want something simpler. The following might do what you want. update shops setitemsqty = ( select count(*) from items i1 where i1.shopid = shops.shopid ) -- 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] Updating with a subselect
On Wed, 23 Apr 2008, Leandro Casadei wrote: On Wed, Apr 23, 2008 at 10:59 AM, Stephan Szabo [EMAIL PROTECTED] wrote: On Tue, 22 Apr 2008, Leandro Casadei wrote: Hi, I need to update a field from a table based in a count. This is the query: updateshops setitemsqty = ( select count(*) from items i1 join shops s1 on i1.shopid = s1.shopid where s1.shopid = s0.shopid ) from shops s0 I think you'll actually want something simpler. The following might do what you want. update shops setitemsqty = ( select count(*) from items i1 where i1.shopid = shops.shopid ) Yes, thanks. I've received a similar answer in the PostgreSQL Forums. I don't know why the join did't work. I had to do this with another table, and the subselect needed a few joins, but I have replaced them with the table names separated by commas and it worked too. Might this be some kind of bug? I don't think so. It's just an unconstrained join. If you were to think about the select that the original update would be like, it'd be like: select (select count(*) from items i1 join shops s1 on i1.shopid=s1.shopid where s1.shopid = s0.shopid) from shops, shops s0; So, it's an unconstrained join of shops and s0. In theory, I think you could have also made the select work by adding a WHERE s0.shopid=shops.shopid, but since there is a much simpler version for that case, it seemed to make more sense to give the simplified one. -- 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] SQL injection, php and queueing multiple statement
On Sun, 13 Apr 2008, Ivan Sergio Borgonovo wrote: On Sun, 13 Apr 2008 16:02:35 +0800 Craig Ringer [EMAIL PROTECTED] wrote: I think this logic is already somewhere in the driver or the pg engine. Whatever you write at the application level a) risk to be a duplication of part of the parser b) risk to be less smart than the parser itself and let slip something. ... in which case it sounds like you need to extend the Pg DB interface to do what you want. It might be worth hacking together a proof of concept and posting it to -hackers and the PHP interface maintainers, along with a rationale for its inclusion. I wish I'd be so familiar with pg C code. And it looks as if such a thing won't be that welcome. Well, Tom suggested making the PHP interface optionally use PQexecParams rather than PQexec even when using a full query string with no parameters as that interface doesn't support multiple queries, so I don't think it's necessarily entirely unwelcome - of course, we're not the PHP team, so they might view it differently. One issue is that it appears that PHP's interface tries to support cases where the libpq version doesn't have PQexecParams, and you'd probably be best to follow the existing style, only using PQexecParams if HAVE_PQEXECPARAMS and the configuration option is set. There appear to be 15 calls to PQexec inside the PHP ext/pgsql.c for the version I have of PHP. 7 of them appear to use a constant string in the call, so don't necessarily need to change. A few of the others are generated single queries for metadata and the like and probably don't need to be configurable to allow multiple queries but merely on HAVE_PQEXECPARAMS. -- 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] mac ports question
On Sat, 5 Apr 2008, Tom Allison wrote: I ran into a problem today where somewhere my port of postgresql82 just stopped working. I'm largely an idiot on Mac because I use is as a workstation/development box and do most of the real system related work on my debian boxes. But I don't know how to get the port working again. Then I saw there is a new version 8.3 in port. So, if I upgrade does anyone know if this cleanly removes version 8.2 from the box so I don't have to start carrying multiple versions? It won't remove 8.2 automatically. You'd have to ask port to deactivate and uninstall it. Unfortunately, you may find it complains about dependencies when you do that. Are you using the server as well, or just the client components? If the server, AFAIK it also won't try to do any database migration, which probably isn't a huge problem on a dev system, but could be. -- 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] mac ports question
On Sat, 5 Apr 2008, Tom Allison wrote: If it doesn't remove the 8.2 then I guess I can migrate it. But that requires that I still need to get 8.2 running. Right now it complains that it can't find a listening socket at /tmp/... (localhost mode). And I can't find the configuration files in this set-up. I do have this running: /opt/local/bin/daemondo --label=postgresql82-server --start-cmd /opt/local/etc/LaunchDaemons/org.macports.postgresql82-server/postgresql82-server.wrapper start ; --stop-c But that doesn't actually mean anything to me other that I guess it's trying to start. And it's stuck somewhere. And no logs. I believe if it's getting far enough to actually try running PostgreSQL the logs would be being put somewhere like /opt/local/var/log/postgresql82 (or at least the 83 port seems to put it in a postgresql83 from there) -- 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 insert values into complex type field
On Fri, 4 Apr 2008 [EMAIL PROTECTED] wrote:
hi all, i want to know how to insert values into the field which is a complex
type. In fact it is a complex type which also include a complex type. The
following is its definition:
create TYPE lifetime as( strattime date, endtime date);
create TYPE attributetype as( ID numeric, address character(50),
periodspan lifetime);
create TABLE attribute2005( gid serial, allfield attributetype);
now i want to insert data into the table attribute2005 but always failure so
wish someone can help.
my sql is following:
INSERT INTO attribute2005
VALUES(1,(23,'ee','ttt',('2005-01-01','2005-12-31')));
I'm running on 8.3, but in that version at least, it looks like you have
a few options for the values and one of these should hopefully work in
8.2.
VALUES (1, ROW(23, 'ee', ROW('2005-01-01', '2005-12-31')));
VALUES (1, '(23,ee,(2005-01-01,2005-12-31))')
There are other slight variations on this second one, you can remove the
double quotes around ee and it looks like you can add double quotes around
the dates, etc.
It also looks like your attibute type above only had 2 scalars and the
complex type rather than three, so I've dropped the 'ttt' for the examples
above.
--
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] unexpected results with NOT IN query
On Thu, 20 Mar 2008, Mason Hale wrote: Hello -- I'm getting some unexpected results with a NOT IN query -- this is on 8.2.5. This is the query in question: prod_2= select id from feed_download_task where id in (02466,141701504) and id not in (select last_feed_download_task_id from subscription); Is it possible for last_feed_download_task_id be NULL? If so, then then id not in (...) will not ever return true due to the way comparisons with NULLs work -- basically, it can't tell if the id is in the other table because id = NULL is unknown, so it thus can't tell that it's not in the other table either, so you could end up with neither in nor not in returning the row. -- 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] Confused about CASE
On Sat, 1 Mar 2008, Thomas Kellerer wrote:
I was writing a statement retrieve dependency information out of the
system catalog, when I noticed something that I didn't expect.
I wanted to use the following statement to translate the relkind
column to a more descriptive value:
select c.relname
case
when c.relkind in ('t','r') then 'table'
when c.relkind = 'i' then 'index'
when c.relkind = 'S' then 'sequence'
when c.relkind = 'v' then 'view'
else c.relkind
end as mykind
from pg_class c
;
The idea is that for anything else than 't', 'r', 'i', 'S' or 'v' it should
simply return the value of relkind. In the other cases I want my value.
But for some reason this returns the value of relkind for all rows. When I
remove the else c.relkind part, it works as expected.
Actually, it doesn't exactly in my tests... for sequences it will
apparently return 's' not 'S'.
It looks like the problem is that relkind is of the somewhat odd
PostgreSQL type char not an actual char(1), so with the else in there it
appears to try to force the unknown literals into that type which only
takes the first character. It will probably work if you cast in the else,
like else CAST(c.relkind as CHAR(1)).
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
Re: [GENERAL] bug in 8.3? foreign key refers to different type
On Tue, 26 Feb 2008, craigp wrote: These create table commands succeed, even tho the foreign key refers to a 'different' type (int2 product_id column refers to an int8 column): The requirements in recent SQL specs appears to be that the column types are comparable, not the same. SQL2003 11.8 referential constraint definition The declared type of each referencing column shall be comparable to the declared type of the corresponding referenced column. As far as I can tell the spec requires two numbers to be comparable, which would make a failure for numeric or real an actual bug, but I don't have an 8.3 system available at the moment to confirm against. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [HACKERS] [GENERAL] deadlock with truncate and foreing keys
On Mon, 18 Feb 2008, Tom Lane wrote: Alexey Nalbat [EMAIL PROTECTED] writes: create table t1 ( id integer primary key, name text ); create table t2 ( id integer references t1 ); insert into t1 values ( 1 ); insert into t2 values ( 1 ); Then two concurrent transactions start. /* 1 */ begin; /* 1 */ truncate t2; /* 2 */ begin; /* 2 */ update t1 set name='foo' where id=1; /* 1 */ insert into t2 values ( 1 ); Here we have deadlock. Hmm, this happens because RI_FKey_keyequal_upd_pk does fk_rel = heap_open(riinfo.fk_relid, AccessShareLock); but right offhand I see no reason for it to do so --- it doesn't *do* anything with fk_rel except close it again. Likewise RI_FKey_keyequal_upd_fk doesn't seem to really need to touch the pk_rel. Is there something I'm missing in that? Maybe this is a vestige of earlier coding that did need to touch both rels to perform the keysequal check? Probably something like that - maybe ri_BuildQueryKeyFull might have needed it open. Actually, I'm wondering if the ri_BuildQueryKeyFull call is also unnecessary now - I don't think we ever use the qkey that comes out of it unless I'm missing some code. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] SELECT CAST(123 AS char) - 1
On Sat, 16 Feb 2008, Tom Lane wrote: Stephan Szabo [EMAIL PROTECTED] writes: On Tue, 12 Feb 2008, Tom Lane wrote: Also, section 6.10 cast specification defines an explicit cast to a fixed-length string type as truncating or padding to the target length (LTD): Are you sure that's the correct section to be using? Isn't that 6.10 General Rules 5c which is if the source type is a fixed or variable length character string? Wouldn't the correct place for an int-char conversion be 5a or am I misreading it? Hm, good point, so really we ought to have a separate casting path for numeric types to char(n). However, this section still doesn't offer any support for the OP's desire to auto-size the result; it says that you get an error if the result doesn't fit in the declared length: Yeah. Although, IIRC, it was one of the options he mentioned as being better than getting the first character but not what he really wanted. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] SELECT CAST(123 AS char) - 1
On Sat, 16 Feb 2008, Ken Johanson wrote: Tom Lane wrote: Hm, good point, so really we ought to have a separate casting path for numeric types to char(n). However, this section still doesn't offer any support for the OP's desire to auto-size the result; it says that you get an error if the result doesn't fit in the declared length: iv) Otherwise, an exception condition is raised: data exception- string data, right truncation. I don't believe the size is being declared in the OP's (subject line) example: SELECT CAST(123 AS char) The other part of Tom's quotes still apply: If length is omitted then a length of 1 is implicit. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] SELECT CAST(123 AS char) - 1
[Way behind on reading stuff - so I hope this wasn't covered later] On Tue, 12 Feb 2008, Tom Lane wrote: Ken Johanson [EMAIL PROTECTED] writes: For sake of interoperability (and using an API that requires String-type hashtable keys), I'm trying to find a single CAST (int - var/char) syntax that works between the most databases. Only char seems to be a candidate, but in 8.3 casting from an integer outputs only the first char... Is this a bug, or would someone like to horrify me by stating something like spec says this is correct. :-) Okay: the spec says this is correct. SQL92 section 6.1 data type quoth character string type ::= CHARACTER [ left paren length right paren ] | CHAR [ left paren length right paren ] ... 4) If length is omitted, then a length of 1 is implicit. Therefore, writing just char is defined as equivalent to char(1). Also, section 6.10 cast specification defines an explicit cast to a fixed-length string type as truncating or padding to the target length (LTD): Case: i) If the length in characters of SV is equal to LTD, then TV is SV. ii) If the length in characters of SV is larger than LTD, then TV is the first LTD characters of SV. If any of the re- maining characters of SV are non-space characters, then a completion condition is raised: warning-string data, right truncation. iii) If the length in characters M of SV is smaller than LTD, then TV is SV extended on the right by LTD-M spaces. Are you sure that's the correct section to be using? Isn't that 6.10 General Rules 5c which is if the source type is a fixed or variable length character string? Wouldn't the correct place for an int-char conversion be 5a or am I misreading it? 5) If TD is fixed-length character string, then let LTD be the length in characters of TD. Case: a) If SD is exact numeric, then let YP be the shortest character string that conforms to the definition of exact numeric literal in Subclause 5.3, literal, whose scale is the same as the scale of SD and whose interpreted value is the absolute value of SV. If SV is less than 0, then let Y be the result of '-' | YP Otherwise, let Y be YP. Case: i) If Y contains any SQL language character that is not in the repertoire of TD, then an exception condition is raised: data exception-invalid character value for cast. ii) If the length in characters LY of Y is equal to LTD, then TV is Y. iii) If the length in characters LY of Y is less than LTD, then TV is Y extended on the right by LTD-LY spaces. iv) Otherwise, an exception condition is raised: data exception- string data, right truncation. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] 8.2/8.3 incompatibility
On Thu, 7 Feb 2008, Harald Fuchs wrote:
This works fine in 8.2.4, but 8.3.0 rejects the ALTER TABLE with the
following (somewhat misleading) error message:
ERROR: insert or update on table t2 violates foreign key constraint
t2_t1id_fk
DETAIL: Key (t1id)=(t1id1) is not present in table t1.
If the types were considered not comparable, you should have gotten a
message to that effect rather than a not present message. More to the
point that comparison should have succeeded I think. What do the following
give?
select * from t1 where id=CAST('t1id1' as VARCHAR(5));
select * from ONLY t2 fk LEFT OUTER JOIN ONLY t1 pk ON
(pk.id = fk.t1id) WHERE pk.id IS NULL;
---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
Re: [GENERAL] turning off notices
On Sat, 19 Jan 2008, Sue Fitt wrote: Hi All, I'm having trouble with turning off notices. Within psql I use \set VERBOSITY terse, which is fine. However, using psql -c I am having trouble. It seems I should be able to use psql -qc 'mycommand' but I am still getting notices output, e.g. psql -d combilex -qc 'SELECT * FROM show(1135311);' NOTICE: phonotactic error, please check transcription CONTEXT: SQL statement SELECT etc Is there a way to turn these notices off other than reconfiguring the conf file and restarting the database? I am using 8.1.3. If you want no notices, you could try set client_min_messages TO WARNING. ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] why it doesn't work? referential integrity
On Sat, 11 Aug 2007, Pavel Stehule wrote: Hello I found strange postgresql's behave. Can somebody explain it? There's a bug since it should work for any number, but we've likely missed something. I'm not sure why 2 references work, as I'd expect it to stop working after 1 with the likely causes, but one of the constraint checks is happening before the row is finished being updated. I don't think it'll help for this case (since it revolved around multiple tables), but could you try the patch from http://archives.postgresql.org/pgsql-bugs/2007-05/msg00177.php to see if it helps this case? ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] why is the LIMIT clause slowing down this SELECT?
On Wed, 1 Aug 2007, Scott Marlowe wrote: On 8/1/07, Mason Hale [EMAIL PROTECTED] wrote: On a 8.1.9 version database that has been recently vacuumed and analyzed, I'm seeing some dramatic performance degradation if a limit clause is included in the query. This seems counter-intuitive to me. Here's the query and explain plan WITH the LIMIT clause: SELECT * FROM topic_feed WHERE topic_id = 106947234 ORDER BY score DESC LIMIT 25 Limit (cost=0.00..651.69 rows=25 width=29) (actual time=72644.652..72655.029 rows=25 loops=1) - Index Scan Backward using topic_feed_score_index on topic_feed (cost=0.00..21219.08 rows=814 width=29) (actual time=72644.644..72654.855 rows=25 loops=1) Filter: (topic_id = 106947234) Total runtime: 72655.733 ms == and now WITHOUT the LIMIT clause: SELECT * FROM topic_feed WHERE topic_id = 106947234 ORDER BY score DESC Sort (cost=1683.75..1685.78 rows=814 width=29) (actual time=900.553..902.267 rows=492 loops=1) Sort Key: score - Bitmap Heap Scan on topic_feed (cost=7.85..1644.40 rows=814 width=29) (actual time=307.900..897.993 rows=492 loops=1) Recheck Cond: (topic_id = 106947234) - Bitmap Index Scan on index_topic_feed_on_topic_id_and_feed_id (cost=0.00..7.85 rows=814 width=0) (actual time=213.205..213.205 rows=2460 loops=1) Index Cond: (topic_id = 106947234) Total runtime: 904.049 ms Something seems wrong here. The cost of the second plan adds up to 1685, the cost of the first plan adds up to 651.69 with an intermediate step that adds up to 21219.08. ??? I thought the outer parts of the plan always contained the inner parts? This doesn't make sense. I think it's because the top node is a limit node over a node that doesn't need to run to completion in order to complete the request so it's expecting an output cost about 25/814ths (limit 25 over 814 estimated rows) of the input cost as it expects to only run that fraction of the inner plan. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] query to match '\N'
On Fri, 27 Jul 2007, pc wrote: Hi, I have a table test with columns col1 col2.col2 contains an entry '\N' .I want to select all entries which have '\N' in col2.How do i do that? select * from test where col2 like '\N' ; select * from test where col2 like '\\N' ; select * from test where col2 like '\\N' escape ''; and select * from test where col2 like 'N'; will probably work. If you're using a recent version and turn on standard_conforming_strings you can halve the number of backslashes, see below. --- On 8.2.4 with standard_conforming_strings=off (and escape_string_warning=off) sszabo= select '\N'; ?column? -- N (1 row) sszabo= select '\\N'; ?column? -- \N (1 row) sszabo= select '\\N' like '\\N'; ?column? -- f (1 row) sszabo= select '\\N' like 'N'; ?column? -- t (1 row) sszabo= select '\\N' like '\\N' escape ''; ?column? -- t (1 row) and with standard_conforming_strings=on sszabo= select '\N'; ?column? -- \N (1 row) sszabo= select '\\N'; ?column? -- \\N (1 row) sszabo= select '\N' like '\N'; ?column? -- f (1 row) sszabo= select '\N' like '\\N'; ?column? -- t (1 row) sszabo= select '\N' like '\N' escape ''; ?column? -- t (1 row) ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Delete/update with limit
On Tue, 24 Jul 2007, Gregory Stark wrote: Csaba Nagy [EMAIL PROTECTED] writes: Unfortunately the stuff that makes a ctid=value nice doesn't seem to be used when you're doing an in. It's possible that a function that does something like for rec in select ctid from my_table limit 10 loop delete from my_table where ctid=rec.ctid; end loop might do okay, but I haven't tried it. OK, I think this will work. It would be nice though to have the 'ctid in' trick work just as well as 'ctid = ' ... Unfortunately I don't think this will work. Multiple backends will happily pick up the same ctid in their selects and then try to delete the same records. I'm pretty sure he said that the batch processing (and the delete) would only be happening from one backend at a time, no concurrency on that portion, merely concurrency with the large volume of inserts. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Delete/update with limit
On Tue, 24 Jul 2007, Csaba Nagy wrote: How about using the following? delete from table where ctid in (select ctid from table limit num); I actually checked this out before starting this thread, and the plan looked like: explain delete from my_table where ctid in (select ctid from my_table limit 10); Unfortunately the stuff that makes a ctid=value nice doesn't seem to be used when you're doing an in. It's possible that a function that does something like for rec in select ctid from my_table limit 10 loop delete from my_table where ctid=rec.ctid; end loop might do okay, but I haven't tried it. ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] String trim function - possible bug?
On Wed, 6 Jun 2007, Woody Woodring wrote: I am seeing weirdness using the trim function on a string: This works as expected: SELECT 'dhct:bn', trim(leading 'dhct:' from 'dhct:bn'); ?column? | ltrim --+--- dhct:bn | bn (1 row) However it fails for these cases: SELECT 'dhct:dn', trim(leading 'dhct:' from 'dhct:dn'); ?column? | ltrim --+--- dhct:dn | n (1 row) The 8.2 docs give this as the description in the table: Remove the longest string containing only the characters (a space by default) from the start/end/both ends of the string That implies that with characters 'dhct:' the string to remove is 'dhct:d' because that's the longest leading string made up of those characters. Maybe a form using something like regexp_replace might work better for you. ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] Lock contention, docs vs. reality
On Sun, 22 Apr 2007, Roland Turner wrote: I'm working with 7.4, but the 8.2 docs[1] have the same apparent error: ROW SHARE Conflicts with the EXCLUSIVE and ACCESS EXCLUSIVE lock modes. The SELECT FOR UPDATE and SELECT FOR SHARE commands acquire a lock of this mode on the target table(s) (in addition to ACCESS SHARE locks on any other tables that are referenced but not selected FOR UPDATE/FOR SHARE). If that conflict list were correct, then ROW SHARE wouldn't conflict with itself, much less with ROW EXCLUSIVE (required to prevent INSERT/UPDATE/DELETE); commonsense dictates that it should conflict with both, and experiment demonstrates that it actually does so. The list in question revolves around table-level locks. Those statements mentioned also take out locks on affected rows. You should be able select for update one row while updating a different row in the same table. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] How to disable duplicate columns
On Sun, 8 Apr 2007, Andrus wrote: I have tables with large number of columns some of which are duplicate. I need to use SELECT t1.*, t2.* FROM t1 join t2 using (t) since I don't know all column names of t1 and t2 tables at design time. In this case PostgreSQL returns table with duplicate columns. How to force Postgres to return only first table column when second table contains column with same name? There are a few cases where duplicate columns are trimmed, such as select * from something with a join ... using or natural join only should result in one output column for the joined upon column names. Code to reproduce: create table t1 ( id integer /*, a lot of other columns */ ); create table t2 ( id integer /*, a lot of other columns */ ); create table t3 as select t1.*,t2.* from t1 join t2 using (id); If only id were duplicated, then select * from t1 join t2 using(id) should work. If other columns are duplicated, then that won't work, but generally just choosing the first column with a name seems bizarre in that case. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: RES: [GENERAL] Order by behaviour
On Thu, 29 Mar 2007, Carlos H. Reimer wrote: Humm, ok, it is clear now. And is there a way to change something in this behaviour, like not ignore spaces and some type of symbols? Well, right now it's generally determined by your OS's definition of the locale you've chosen. You might be able to pick another locale which has different behavior if your system supports one or gives you a reasonable way to create one, or as the last ditch attempt, locale C for bytewise ordering. However, I think you'll need to recreate the database if you change the locale. ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] Order by behaviour
On Wed, 28 Mar 2007, Carlos H. Reimer wrote: Hi, We have a PostgreSQL 8.0.6 cluster configured with lc_collate=pt_BR.UTF-8 and when we run the following SELECT: SELECT substr(nomerazao,1,4), ascii(substr(nomerazao,1,1)), ascii(substr(nomerazao,2,1)) from spunico.unico order by nomerazao; is returning: substr | ascii | ascii +---+--- |32 | 0 |32 | 0 1000 |49 |48 1.DI |49 |46 1? R |49 | 176 2M C |50 |77 3A.G |51 |65 A. A |65 |46 AABA |65 |65 A.A. |65 |46 A.AG |65 |46 A.A. |65 |46 A.A. |65 |46 ABAS |65 |66 ABAS |65 |66 ABAT |65 |66 A.B. |65 |46 A.B. |65 |46 ABCC |65 |66 A.B. |65 |46 A.B. |65 |46 Are not the lines out of order or is it a normal behaviour for a server with lc_collate=pt_BR.UTF-8? Many collations ignore spaces and symbols on the first pass, so, for example you might have A Z AB despite the fact that a space has a lower value than a B. ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] deleting a foreign key that has no references
On Mon, 19 Mar 2007, Glen W. Mabey wrote: Hello, I'm using 8.1.8, and I have a situation where a record in one table is only meaningful when it is referenced via foreign key by one or more records in any one of several tables. So, really what I want is when one of the referring records is deleted, to have a trigger check to see if it was the last one to use that foreign key, and if so, to delete that other record, too. My first implementation of this functionality was to write a trigger function that executed a COUNT(*) on all of the tables that could have a reference in them. That became way too slow for the number of records in these tables. Then, I tried to setting ON DELETE RESTRICT or NO ACTION on the foreign key constraint, and then trying to catch the exception thrown when a deletion attempt is made on the record. However, it seems that this PL/pgsql snippet fails to catch such an error: BEGIN EXCEPTION WHEN RAISE_EXCEPTION THEN RETURN NULL; WHEN OTHERS THEN RETURN NULL; END; Was that the actual function you used or just a shortened version? A function like that with a delete of the referenced table in the body for the appropriate key appeared to have reasonable behavior on my 8.2 system with an immediate constraint, but I didn't do very much testing. One issue is that to test the insert of a row into the referenced table you'd probably need to defer a check that the row is referenced in order to have time to insert referencing rows. But, really, I just want to be able to test to see how many references there are to a key. Is there some way to do that? Currently, not apart from selecting on the referencing table. ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] [Bulk] Re: quoted identifier behaviour
On Wed, 14 Mar 2007, Randall Smith wrote: Stephan Szabo wrote: On Wed, 14 Mar 2007, Randall Smith wrote: Scott Marlowe wrote: This whole discussion is reminding me of one of my personal mantras, and that is that relying on artifacts of behaviour is generally a bad idea. For instance, many databases accept != for not equal, but the sql standard quite clearly says it's . If you're relying on case folding meaning that you don't have to consistently use the same capitalization when referring to variables, table names, people, or anything else, you're asking for trouble down the line, and for little or no real gain today. I know that a lot of times we are stuck with some commercial package that we can't do anything to fix, so I'm not aiming this comment at the average dba, but at the developer. Yea, this is a commercial package, but it's actually doing it right. Since it doesn't know how a user will name a table or column, it always calls them as quoted strings in upper case which is standards compliant, but doesn't work with PG. So if a user names a table 55 and mine, it calls 55 AND MINE and for foo, it calls FOO. Looks like they did it right to me. Maybe, but the 55 and mine example may or may not actually work. 55 and mine isn't a valid regular identifier. 55 and mine would be a valid identifier, but that's not the same identifier as 55 AND MINE. Your right. Its not a correct example. I think the point is clear, though. Well, I was arguing about whether the app was doing it right. Assuming that you can uppercase and put quotes around an arbitrary table name is wrong too, because that's only valid for regular identifiers in SQL, so I was wondering if it had support for things that were created as quoted identifiers (which you might be able to use as a temporary workaround). This isn't an argument against putting spec compliant behavior into PostgreSQL, just more of a point that getting this right through the whole system from app to db can be somewhat tricky even in the best case. ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] [Bulk] Re: quoted identifier behaviour
On Wed, 14 Mar 2007, Randall Smith wrote: Scott Marlowe wrote: This whole discussion is reminding me of one of my personal mantras, and that is that relying on artifacts of behaviour is generally a bad idea. For instance, many databases accept != for not equal, but the sql standard quite clearly says it's . If you're relying on case folding meaning that you don't have to consistently use the same capitalization when referring to variables, table names, people, or anything else, you're asking for trouble down the line, and for little or no real gain today. I know that a lot of times we are stuck with some commercial package that we can't do anything to fix, so I'm not aiming this comment at the average dba, but at the developer. Yea, this is a commercial package, but it's actually doing it right. Since it doesn't know how a user will name a table or column, it always calls them as quoted strings in upper case which is standards compliant, but doesn't work with PG. So if a user names a table 55 and mine, it calls 55 AND MINE and for foo, it calls FOO. Looks like they did it right to me. Maybe, but the 55 and mine example may or may not actually work. 55 and mine isn't a valid regular identifier. 55 and mine would be a valid identifier, but that's not the same identifier as 55 AND MINE. ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] complex referential integrity constraints
On Fri, 23 Feb 2007, Joris Dobbelsteen wrote:
-Original Message-
From: Martijn van Oosterhout [mailto:[EMAIL PROTECTED]
Sent: vrijdag 23 februari 2007 9:50
To: Joris Dobbelsteen
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] complex referential integrity constraints
On Fri, Feb 23, 2007 at 12:41:25AM +0100, Joris Dobbelsteen wrote:
Reasonably. I have no idea what visibility rules would make any
difference at all. AIUI a foreign key just takes a shared
lock on the
referenced row and all the magic of MVCC makes sure the row exists
when the transaction completes.
Try this:
(sorry for any typo's in SQL, if they exist)
snip
Well, I took a look at the RI code and the only stuff I saw
that looked interesting was this:
utils/adt/ri_triggers.c:
if (IsXactIsoLevelSerializable detectNewRows)
{
CommandCounterIncrement(); /* be
sure all my own work is visible */
test_snapshot = CopySnapshot(GetLatestSnapshot());
crosscheck_snapshot =
CopySnapshot(GetTransactionSnapshot());
}
It then proceeds to use that snapshot to execute the query to
get the share lock.
It's probably true that other PL's can't do this directly. Not
sure how to deal with that. I got confused because I thought
the first version of RI did use straight pl/pgsql functions,
so I thought that was enough.
You got it right...
/*
* SPI_execute_snapshot -- identical to SPI_execute_plan, except that we
allow
* the caller to specify exactly which snapshots to use. This is
currently
* not documented in spi.sgml because it is only intended for use by RI
* triggers.
*
* Passing snapshot == InvalidSnapshot will select the normal behavior
of
* fetching a new snapshot for each query.
*/
int
SPI_execute_snapshot(void *plan,
Datum *Values, const char
*Nulls,
Snapshot snapshot, Snapshot
crosscheck_snapshot,
bool read_only, long tcount)
They got the point right: only intended for use by RI triggers. That's
exactly the type I'm trying to build ;)
They are exposed to the C versions (its in include/executor/spi.h), but
to me it looks a bit cumbersome to have triggers written in C.
I was wondering if some sort of generator might work. Something that would
take what you're trying to do and generate the triggers for you, but I
haven't really worked out what that'd look like.
What would be a good way to expose this to normal PL triggers? Since
this would open a new set of possibilities...
As part of a create trigger ... for referencial integrity?
As an extension to a statement?
Special construct in the languages?
I think the first thing to do is to figure out what such triggers need to
do. Does such a trigger need to potentially run some queries on the normal
snapshot? Does it potentially need different snapshots for different
statements or is only one special snapshot sufficient? And other such
questions. From there, a -hackers discussion might be meaningful.
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org/
Re: [GENERAL] complex referential integrity constraints
On Thu, 22 Feb 2007, Joris Dobbelsteen wrote: -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Martijn van Oosterhout Sent: donderdag 22 februari 2007 18:17 To: Joris Dobbelsteen Cc: Robert Haas; pgsql-general@postgresql.org Subject: Re: [GENERAL] complex referential integrity constraints On Thu, Feb 22, 2007 at 05:28:35PM +0100, Joris Dobbelsteen wrote: Even worse, I don't you can guarentee that this constraint is enforced at all times. That means, not if you are using triggers. The only option seems using foreign keys and put in a lot of redundant data. Err, foreign keys are implemented using triggers, so this statement is self-contradictary. Are you really sure they are executed under the same visibility rules? IIRC, the ri triggers use calls that you aren't able to get at in triggers written in any of the PLs, but I think you should be able to replicate the feat in a trigger written in C. ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] invalid input syntax for integer: NULL
On Tue, 20 Feb 2007, Yonatan Ben-Nes wrote:
Hi everyone,
I'm trying to write a PL/pgSQL function which execute an insert, I encounter
a problem when I try to insert NULL value into an integer field.
The following code is for reproducing:
CREATE TABLE test(
bh INT8
);
CREATE OR REPLACE FUNCTION testinsertion(intornull bigint) RETURNS text AS
$$
DECLARE
BEGIN
RETURN 'INSERT INTO test (bh) VALUES ('||COALESCE(intornull, 'NULL')||')';
I think you'd need something like
COALESCE(CAST(intornull AS TEXT), 'NULL')
in order to make that work. You want the output to effectively be a string
which contains the int to be concatenated with the other strings or the
string 'NULL' to be concatentated with the other strings.
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
Re: [GENERAL] invalid regular expression: invalid backreference number
On Sun, 18 Feb 2007, Jeff Ross wrote: Tom Lane wrote: Since ceil() produces float8 which does not implicitly cast to int, this call has probably never done what you thought --- AFAICS it will cast all the arguments to text and invoke substring(text,text,text) which treats its second argument as a SQL99 regular expression. I doubt that it's useful to figure out exactly what changed to make it fail more obviously than before --- I think the problem is that you'd better cast the ceil() result to int. [ObRant: still another example of why implicit casts to text are evil.] To debug this I've extracted the code into its own function: CREATE FUNCTION gen_password() RETURNS text AS $$ DECLARE password text; chars := 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789'; BEGIN FOR i IN 1..9 LOOP password := password || SUBSTRING(chars, ceil(random()*LENGTH(chars))::int, 1); END LOOP; return password; END; $$ LANGUAGE plpgsql; when I try to generate the function with this I get the following error: psql -f create_password.sql wykids psql:create_password.sql:12: LOG: statement: CREATE FUNCTION gen_password() RETURNS text AS $$ DECLARE password text; chars := [snipped] psql:create_password.sql:12: ERROR: invalid type name CONTEXT: compile of PL/pgSQL function gen_password near line 3 Given the context and function, I'd say it's complaining because you didn't put a type after chars and before the := for the initializer. Changing it to chars text := ... should make that work. In addition, the default initialized value for password will be a NULL which probably won't do what you want either, since NULL || something is NULL, so you probably want password text := '' there. ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] can't CREATE TRIGGER
On Mon, 22 Jan 2007, gustavo halperin wrote: I can't create triggers, when I call for example: ficha= CREATE TRIGGER TRG_persons_id AFTER INSERT ON ficha_ofperson ficha- EXECUTE PROCEDURE add_person_id ( 'family_id', 'person_id' ); , the creation never finish and finally, after many minutes, I kill the creation with Ctrl+c. I try also a simple trigger creation with a function without arguments and also the creation never finish. One possibility is that some transaction has a lock on the table which would block the create trigger, do you have any long running transactions or some kind of transaction pooling that might be keeping the table locked for a long time? Can you create simple triggers on another table? ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] Multi-column constraint behaviour
On Tue, 16 Jan 2007, Bertram Scharpf wrote: Hi, please have a look at these introducing statements: sandbox=# create table q(i integer, t text, primary key (i,t)); sandbox=# create table f(i integer, t text, foreign key (i,t) references q); Now, this is surprising me: sandbox=# insert into f (i,t) values (34,null); INSERT 0 1 sandbox=# select * from f; i | t +--- 34 | What I expected was that the constraint forces all values to be null when there is no referenced value pair. I were bored if I had to fix this behaviour with check constraints for every occurrence of the columns pair. Is there a deeper reason why the foreign key allows not referenced non-null values or is there an easy way to fix the whole behaviour? You're using the default match type (also known as match simple I think) for which the rules are that it passes if there are any nulls or all are non-null and have a matching row. Match full says that either all must be null or all must be non-null and have a matching row. That's probably more like what you want. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Functional Index
On Wed, 22 Nov 2006, Alexander Presber wrote: Hello everybody, I am trying to speed up a query on an integer column by defining an index as follows CREATE INDEX idx_main_subject ON pdb.main (lower(main_subject::text) using varchar_ops); on column main_subject. I had hoped to get speedups for right-fuzzy LIKE-searches, IIRC, unless you're in C locale, you'll want varchar_pattern_ops rather than varchar_ops on the index to make it considered for a LIKE search. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] I'm lost :-( with FOR...IN
On Tue, 7 Nov 2006, Alain Roger wrote: Hi, I' still with my stored procedure : -- Function: SP_U_001(typeofarticle varchar) -- DROP FUNCTION SP_U_001(typeofarticle varchar); CREATE OR REPLACE FUNCTION SP_U_001(IN typeofarticles VARCHAR) RETURNS SETOF active_articles AS $BODY$ DECLARE myrec RECORD; res active_articles; /**/ BEGIN FOR myrec IN select * from articles, articletypes, department where articletypes.articletype_type = $1 AND articles.articletype_id = articletypes.articletype_id AND articles.department_id = department.department_id AND articles.validity_period_end now() LOOP IF (myrec IS NOT NULL) THEN res.article_type := myrec.articletypes.articletype_type; I don't think the column names are going to keep their originating table name inside the record, so the field probably needs to be referred to as myrec.articletype_type not myrec.articletypes.articletype_type. ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Simple stored procedure examples?
On Fri, 3 Nov 2006, novnov wrote:
I'm completely new to pgsql, using 8.1 and pgAdmin III. I'm not finding a lot
of bare bones simple example stored procs that I can learn from. It would be
very helpful if someone could show me some simple code.
In the pgAdmin interface I've been picking SQL as the language, that's the
default it offers.
Say I have a table Item, and fields ItemID (int4) and ItemName (varchar).
What would be the code for
#1 updating ItemName for all rows to 'fox'
#2 updating ItemName for row where ItemID = 2 to 'fox'
#3 updating ItemName for row where ItemID = 3 to a param value passed in
I think something like:
sszabo=# create table Item(ItemID int4, ItemName varchar);
CREATE TABLE
sszabo=# create function f1() returns void as $$update Item set
ItemName='fox';$$ language 'sql';
CREATE FUNCTION
sszabo=# create function f2() returns void as $$update Item set
ItemName='fox' where ItemID=2;$$ language 'sql';
CREATE FUNCTION
sszabo=# create function f3(varchar) returns void as $$update Item set
ItemName=$1 where ItemID=3;$$ language 'sql';
CREATE FUNCTION
sszabo=# insert into Item values (1, 'aaa');
INSERT 0 1
sszabo=# insert into Item values (2, 'bbb');
INSERT 0 1
sszabo=# insert into Item values (3, 'ccc');
INSERT 0 1
sszabo=# select * from Item;
ItemID | ItemName
+--
1 | aaa
2 | bbb
3 | ccc
(3 rows)
sszabo=# select f2();
f2
(1 row)
sszabo=# select * from Item;
ItemID | ItemName
+--
1 | aaa
3 | ccc
2 | fox
(3 rows)
sszabo=# select f1()
sszabo-# ;
f1
(1 row)
sszabo=# select * from Item;
ItemID | ItemName
+--
1 | fox
3 | fox
2 | fox
(3 rows)
sszabo=# select f3('monkey');
f3
(1 row)
sszabo=# select * from Item;
ItemID | ItemName
+--
1 | fox
2 | fox
3 | monkey
(3 rows)
As a note, you'll probably pretty quickly move into things for which SQL
isn't a good fit, so you might want to look at plpgsql as well.
That would be so helpful...I've tried and get errors like ERROR relation
item does not exist, and of course the table Item and the column ItemName
do exist, haven't been able to figure out what the error means.
The names were probably doublequoted when created (possibly automatically
by your creating client application) which means you'd need to double
quote them on use as well. Regular (unquoted) identifiers are case-folded,
so Item != Item.
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Simple stored procedure examples?
On Sat, 4 Nov 2006, novnov wrote: Thanks to both of you for responding. I should have included the code for my own attempt, at #1 which is just as you suggest: update item set itemname = 'fox'; I've tried single, and double quoting the table and field names; call caps to the UPDATE etc, exactly matching the capitalization of the table and field names (really Item and ItemName). I wonder if Item is a reserved word in pgsql? I used an Item table as well, and I can't think of anything between 8.1 and 8.2 beta that would make a difference. If the admin tool quoted the name (which it probably did given the function declaration you showed next), then you have to both double quote and match the capitalization in order to reference the table (none of Item, item or item will match a table created as Item). If you pull out the create functions and calls from my earlier example, do you get the same failures as with your attempt? ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Deleting Problem
On Tue, 31 Oct 2006, Jamie Deppeler wrote: Here is my problem I have a level structure which is 5 levels deep with 6 tables, for this example i will call it table1,table2,table3,table4,table5,table6 (1)table1 (2)table2 (3)table3 (4)table4 (5)table5,table6 (6)table7,table8 table5 and table6 have fk keys pointing to table1,table2,table3,table4. table7 and table 8 have fk keys pointing to table5. Here is my problem i have delete triggers on table5 and table6 which update summary information on table1,table2,table3,table4 if table5 or 6 gets delete, problem i am faced with is when eg table1 record gets deleted i get the following error Error insert or update on table5 violates foreign key constraint table4. They are all link with a cascade delete. We're probably not going to be much help without more information about the details of the triggers and tables involved such as what operations the triggers do to do the summary update and whether they're defined as before or after triggers. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Wrong record type - caused by SELECT order ???
On Fri, 27 Oct 2006, John Cobo wrote:
I am trying to create some functions which return many rows using
plpgsql. This example could be done with SQL, but later I will need
plpglsql. I was constantly getting the wrong record type error with a
couple different functions. Finally I found that if I changed the order
of columns in the SELECT statement then this simple example would work.
Any suggestions as to why this is happening or what I can do to
consistently get such functions to work ? Is there an easier way to do
all this ? (
Well, I think the simple answer is to return next foo rather than rec in
the function.
The longer answer is that in the first case you're returning a record with
an int first and a varchar second and in the second you're return a record
with a varchar first and an int second and category_list is compatible
with the latter and not the former.
select * from list_categories(1,200608);
ERROR: wrong record type supplied in RETURN NEXT
CONTEXT: PL/pgSQL function list_categories line 11 at return next
--
CREATE OR REPLACE FUNCTION list_categories(int4, int4)
RETURNS SETOF category_list AS
$BODY$
DECLARE
foo category_list;
rec RECORD;
BEGIN
FOR rec IN
SELECT c.id, c.category_name FROM categories c WHERE user_id = pUser_id
LOOP
foo.Oid := rec.id;
foo.Ocategory_name := rec.category_name;
RETURN NEXT rec;
END LOOP;
RETURN;
END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE;
However, if I change the order of columns in the SELECT and run the same:
select * from list_categories(1,200608);
Then the function works fine
CREATE OR REPLACE FUNCTION list_categories(int4, int4)
RETURNS SETOF category_list AS
$BODY$
DECLARE
foo category_list;
rec RECORD;
BEGIN
FOR rec IN
SELECT c.category_name, c.id FROM categories c WHERE user_id = pUser_id
LOOP
foo.Oid := rec.id;
foo.Ocategory_name := rec.category_name;
RETURN NEXT rec;
END LOOP;
RETURN;
END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE;
--
The table:
CREATE TABLE categories
(
id int4 NOT NULL DEFAULT nextval('categories_id_seq'::regclass),
user_id int4 NOT NULL,
category_name varchar(45) NOT NULL,
CONSTRAINT categoriesPK PRIMARY KEY (id),
CONSTRAINT categories_userFK FOREIGN KEY (user_id) REFERENCES users (id)
ON UPDATE NO ACTION ON DELETE NO ACTION
)
WITHOUT OIDS;
and TYPE
CREATE TYPE category_list AS
(ocategory_name varchar(60),
oid int4);
---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
Re: [GENERAL] c (lowercase) privilege
On Fri, 20 Oct 2006, Javier Carlos wrote: Does anybody know what's the meaning of the c (lowercase) privilege in PostgreSQL 8.2 Beta? That should be for CONNECT privilege. ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Normal vs Surrogate Primary Keys...
On Sun, 1 Oct 2006, rlee0001 wrote: I know, for example, that by default PostgreSQL assigns every record a small unique identifier called an OID. It seems reasonable then, that when the DBA creates a cascading foreign key to a record, that the DBMS could, instead of storing the record's entire natural key, store only a reference to the OID and abstract/hide this behavior from the environment just as PostgreSQL does with its OID feature now. Of course, this would require that the OID be guaranteed unique, which I don't beleave is the case in the current versions. This would completely eliminate concerns related to the performance of cascading updates because no actual cascade would take place, but rather the update would affect all referencing records implicitly via the abstraction. Well, that alone isn't enough I think. MATCH SIMPLE allows you to pass the constraint for a row if any of the columns in a multi-column foreign key are NULL, so there isn't always a matching row, but there's also meaningful information in the column values. MATCH PARTIAL (which we admittedly don't support yet) allows you to have a valid key if the non-NULL portions of the multi-column foreign key match to one or more rows in the referenced table, so there may be more than one matching row. The all NULL case is pretty easy to handle in general. In addition, AFAICT for cascades you would potentially be trading the cost at cascade time with the cost at select time, so that would itself not always be a win. Also, I don't see how you get away with not needing two indexes on the referenced table to do this well unless you're storing something like a ctid which has its own problems with updates. I think there may be some better options than what we've got, but there's tradeoffs as well. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] text to point conversion not working. ( cannot cast
On Wed, 27 Sep 2006, Dan Libby wrote: Background: I have a hierarchical table where I have coordinate data for only the leaf nodes. I therefore want to find the center of all the leaf nodes under a given parent node, and set the parent node coordinate to that center point. I can calcululate that center point using aggregate functions (min, max) to find the necessary x,y values.So my query would look something like this: update parent_table set col = (select (max(pnt[0])-min(pnt[0]))/2+min(pnt[0]) || ',' || max(pnt[1])-max(pnt[1])/2+min(pnt[1]) from point_tmp where condition) where condition2 ; Where point_tmp.tmp is defined as a point column. However, when I try to do it, I get a similar error: column col is of type point but expression is of type text If the above task can be performed some other way, perhaps I don't require string concatenation I don't have 8.0.x to check, but there's likely a point(double precision, double precision) function you can use. ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] Is this logical?
On Sun, 10 Sep 2006, Behrang Saeedzadeh wrote: Shouldn't this create statement trigger an error? create table bar (col1 int not null default null); Shouldn't I be forbidden to insert null values into a non null column? I think it should forbid it when the default actually comes into play like on insert or update, but not at create table time as there are no rows for which the constraint fails. For example after that: sszabo=# insert into bar default values; ERROR: null value in column col1 violates not-null constraint ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] Is this logical?
On Sun, 10 Sep 2006, Behrang Saeedzadeh wrote: But not null is in contradiction with default null so the create statement should not proceed successfuly IMHO. The fact that the default value isn't going to pass a constraint means that it's invalid to not provide a value or use default, yes, but I don't agree that it's necessarily a real contradiction in the definition, especially given that AFAIK there's no way to opt out of having a default (apart from a construct like the one given). ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] select * from users where user_id NOT in (select
On Thu, 17 Aug 2006, Alexander Farber wrote: I have this strange problem that the following statement works: NULLs are not your friends. :( phpbb= select user_id, username from phpbb_users phpbb- where user_id in (select ban_userid from phpbb_banlist); user_id | username -+-- 3 | La-Li (1 row) But the negative one returns nothing: phpbb= select user_id, username from phpbb_users phpbb- where user_id not in (select ban_userid from phpbb_banlist); user_id | username -+-- (0 rows) Sadly, these two look like they would give you all the users rows, but they don't because of the NULL ban_userid. When the subselect returns NULL for at least one row, you fall into this sort of case. x NOT IN (...) is equivalent to NOT(x IN (...)) which is NOT(x = ANY (...)) x = ANY (...) is basically defined as True if x = y is true for some y in the subselect False if x = y is false for all y in the subselect Unknown otherwise Since x = NULL is unknown and not true or false, you fall into the last case with your query and data. Eventhough there are 3 other users in the phpbb_users table: phpbb= select user_id, username from phpbb_users; user_id | username -+--- -1 | Anonymous 3 | La-Li 4 | Vasja 2 | Alex (4 rows) And there is only one user (La-Li, id=3) in the phpbb_banlist: phpbb= select * from phpbb_banlist; ban_id | ban_userid | ban_ip | ban_email +++- 1 | 3 || 4 ||| [EMAIL PROTECTED] (2 rows) ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] Weird join result
On Wed, 16 Aug 2006, Peter Nixonn wrote: I am getting a result for an JOIN that I think is wrong. Maybe its my understanding that is wrong here however, so please be gentle :-) The phones table contains a list of phone numbers and an associated customer ID. The radacct table contains a list of all calls made (RADIUS Accounting records). I am doing the following: SELECT phones.CALLINGSTATIONID, sum(radacct.acctinputoctets) FROM phones LEFT OUTER JOIN radacct ON (phones.CALLINGSTATIONID = radacct.CALLINGSTATIONID) WHERE phones.CUSTID = 1 GROUP BY phones.CALLINGSTATIONID; This query as expected returns 1386 rows (for customer ID 1) which includes a number of rows which have a NULL sum as they have not ever connected. Now, what I want to do is to return the same result set (of 1386 phones), but only for a particular time period. I therefore do the following: SELECT phones.CALLINGSTATIONID, sum(radacct.acctinputoctets) FROM phones LEFT OUTER JOIN radacct ON (phones.CALLINGSTATIONID = radacct.CALLINGSTATIONID) WHERE phones.CUSTID = 1 and radacct.ACCTSTARTTIME BETWEEN '2006-05-17 15:16:42' AND '2006-08-16 15:16:42' GROUP BY phones.CALLINGSTATIONID; This returns 1280 rows, none of which are have a NULL value for sum. This surprised me at first as I thought the WHERE clause should apply before the OUTER JOIN but apparently not. No, in fact it explicitly happens after the join (the order of evaluation in the theoretical model is basically evaluate the from clause as a table then apply where on that table to make a new table and so on). You can either use a subselect in from list or put the condition into the ON to make its evaluation earlier. I then tried the following: SELECT phones.CALLINGSTATIONID, sum(radacct.acctinputoctets) FROM phones LEFT OUTER JOIN radacct ON (phones.CALLINGSTATIONID = radacct.CALLINGSTATIONID) WHERE phones.CUSTID = 1 and (radacct.ACCTSTARTTIME BETWEEN '2006-05-17 15:16:42' AND '2006-08-16 15:16:42' OR radacct.ACCTSTARTTIME ISNULL) GROUP BY phones.CALLINGSTATIONID; This query returns 1368 rows, which includes some NULL values for sum, however still short of the 1386 rows I am looking for. Close, but no cigar! Imagine you had phones (callingstationid = 1, custid = 1) phones (callingstationid = 2, custid = 1) phones (callingstationid = 3, custid = 1) radacct (callingstationid = 1, acctstarttime 2006-05-10 00:00:00) radacct (callingstationid = 2, acctstarttime 2006-08-10 00:00:00) Now, I believe the outer join should above give you (callingstationid = 1, custid = 1, acctstarttime 2006-05-10 00:00:00) (callingstationid = 2, custid = 1, acctstarttime 2006-08-10 00:00:00) (callingstationid = 3, custid = 1, acctstarttime NULL) Then apply the where clause (callingstationid = 2, custid =1, acctstarttime 2006-08-10 00:00:00) (callingstationid = 3, custid =1, acctstarttime NULL) --- With the subselect in from you'd have phones (callingstationid = 1, custid = 1) phones (callingstationid = 2, custid = 1) phones (callingstationid = 3, custid = 1) radacct (callingstationid = 1, acctstarttime 2006-05-10 00:00:00) radacct (callingstationid = 2, acctstarttime 2006-08-10 00:00:00) The first radacct row doesn't pass, so it's not in the subselect output which should then look like: subsel_radacct (callingstationid = 2, acctstarttime 2006-08-10 00:00:00) Then, I believe the outer join should give you (callingstationid = 1, custid = 1, acctstarttime NULL) (callingstationid = 2, custid = 1, acctstarttime 2006-08-10 00:00:00) (callingstationid = 3, custid = 1, acctstarttime NULL) And then you apply the where clause again and all the rows go through. ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] REFERENCE problem with parent_table
On Tue, 15 Aug 2006, gustavo halperin wrote: Hello I need many tables of type id and name, see below: / CREATE TABLE id_names ( idsmallintCONSTRAINT the_id PRIMARY KEY NOT NULL, nametextCONSTRAINT the_name UNIQUE ) WITH OIDS;/ therefore I created these tables with the LIKE operator, see below: /CREATE TABLE like_id_1( LIKE id_names INCLUDING DEFAULTS ) WITH OIDS; CREATE TABLE like_id_2 ( LIKE id_names INCLUDING DEFAULTS ) WITH OIDS; CREATE TABLE like_id_3 / Next I can't create a table with some column reference to any of the last two tables, see below: /database=# CREATE TABLE ref_1 ( id_1 smallint CONSTRAINT the_id_1 REFERENCES like_id_1 (id) ); ERROR: there is no unique constraint matching given keys for referenced table like_id_1/ Obviously if I use id_names instead of like_id_1 every think is fine but my idea is not create thousands of almost same tables with the table name's like the only one difference. Then I thought to use the operator LIKE, but you see, there are a problem. Any Idea about what must I do ?? The LIKE clause doesn't copy the UNIQUE/PRIMARY KEY constraints from id_names. You'll probably need to add the constraint information to the other tables. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] CREATE DATABASE
On Fri, 4 Aug 2006, Nikolay Samokhvalov wrote: On 8/4/06, Nikolay Samokhvalov [EMAIL PROTECTED] wrote: [...] BTW, difference vanishes due to expression power of SQL - it supports session comands in the same context as DDL commands and data manipulation stmts (SQL:200n, 4.33.2.5 'SQL-connection statements'). Sorry, I already see my failure. It is in the fact that Postgres doesn't support that connection statements yet (but some connection things are supported - like changing the CURRENT_ROLE). So, my previous message is interesting only from theoretical point of view. I always try to thing in the manner of SQL standard or use knowledge from books/university ... But the logic is clear, isn't it? Connection is not client operation. I'd read 4.39 differently which seems to imply that the SQL-client handles the connection statements. ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] CASE statement and SETOF values
On Tue, 25 Jul 2006, Christian Schoenebeck wrote: Am Dienstag, 25. Juli 2006 00:01 schrieb Stephan Szabo: The above basically looks like: CASE WHEN search condition THEN value expression ELSE value expression END. In SQL92 at least, the form of value expression which looks like (SELECT ...) is scalar subquery which is limited to 1 column and 1 row. The other subquery forms don't look legal in that position unless they changed that in a later version of the spec. Ok, and is there any way to circumvent this problem? Well, the easiest one is to use a procedural language to get conditional statements. For example, something like the following (untested) plpgsql body: DECLARE r record BEGIN IF (some_condition) THEN FOR r IN SELECT ... LOOP RETURN NEXT r; END LOOP; ELSE FOR r IN SELECT ... LOOP RETURN NEXT r; END LOOP; END IF; RETURN; END; ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] CASE statement and SETOF values
On Mon, 24 Jul 2006, Christian Schoenebeck wrote: Consider the following server side function: CREATE FUNCTION my_function(int4) RETURNS SETOF int8 AS $BODY$ SELECT CASE WHEN (some_condition) THEN ( SELECT ... -- arbitrary select (returning row(s) of int8 values) ) ELSE ( SELECT ... -- arbitrary select (returning row(s) of int8 values) ) END $BODY$ LANGUAGE 'sql' VOLATILE; This function works fine if one of the two inner SELECT statements returns exactly one result (one row), but fails whenever one of them returns more than one result / rows. What is the reason? I mean the function is declared as returning SETOF int8, so why does it expect a scalar? The above basically looks like: CASE WHEN search condition THEN value expression ELSE value expression END. In SQL92 at least, the form of value expression which looks like (SELECT ...) is scalar subquery which is limited to 1 column and 1 row. The other subquery forms don't look legal in that position unless they changed that in a later version of the spec. ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] SQL Standards Compliance With Case
On Wed, 12 Jul 2006, Rich Shepard wrote: I'm trying to assist the XRMS developers port their application to postgres (8.1.x on), and it's almost there. One (perhaps the only) stumbling block is case for table and column (relation and attribute) names. Apparently MySQL allows for mixed case, while postgres wants only lower case. One of the development team asked me to enquire when postgres would be fully compliant with the SQL standard in this reqard. So I'm asking. Not challenging, not complaining, but asking to learn something about case and the SQL standard as implemented in postgres. AFAIK, SQL says that an non-quoted identifier such as Foo is treated as FOO (case-folded to upper). PostgreSQL currently treats it as foo (case-folded to lower). Quoted identifiers are not case-folded and are compared case-sensitive. So, for example my understanding of spec would say: create table Foo(a numeric(10,3)); create table FoO(b numeric(11,4)); -- invalid because this is the same table name as the first create create table foo(a numeric(12,5)); -- valid in SQL, invalid in PostgreSQL create table Foo(a numeric(13,6)); -- valid, that's actually the mixedcase table Foo rather than FOO or: create table Foo(a numeric(10,3)); select * from Foo -- invalid, that's FOO not Foo select * from Foo -- valid create table Foo(a numeric(10,3)); -- folded to FOO select * from foo -- valid select * from foo -- invalid While I would prefer to not read the latest SQL standard specification, I'd like to help resolve the last six errors when I try to install XRMS on my postgres-8.1.4 system. Here's what the install.php script returns: Unable to execute your query. Please correct this error. You may need to update your database structure. ERROR: relation group_id already exists I tried to execute: CREATE INDEX Group_id ON GroupUser (Group_id) These seem to be complaining that there's already a table, view, index, etc with that name already. Is there one being created with a different case that's assuming that it'll preserve case rather than fold? ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] Bug? Changing where distinct occurs produces error?
On Fri, 7 Jul 2006, Michael Loftis wrote: OK I'm either insane or found a bug in 8.1.3 If you execute say: SELECT DISTINCT(ua.user_id),pa.poll_id FROM user_answers ua, poll_answers pa WHERE pa.poll_answer_id = ua.poll_answer_id AND ua.active='Y'; Everything is fine, however if you run SELECT pa.poll_id,DISTINCT(ua.user_id) FROM user_answers ua, poll_answers pa WHERE pa.poll_answer_id = ua.poll_answer_id AND ua.active='Y'; This statement is invalid. DISTINCT is a set quantifier and either comes before the select list or as the first thing in a set function specification. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] ERROR: more than one row returned by a subquery used
On Wed, 3 May 2006, Arjan Vroege wrote: Hello, I have the following Query with Subqueries. This query gives the error : ERROR: more than one row returned by a subquery used as an expression. Is there a solution to solve this problem: Scalar subqueries (like the ones in your select list) can't return more than one row since it'd be effectively random which row's value came out (and if the values are known to have to be the same you can use distinct in the subquery). Given a subquery like this from the query... (SELECT tbl_wk_land.landnaam FROM tbl_wk_land INNER JOIN tbl_wk_landgroep ON tbl_wk_land.landid=tbl_wk_landgroep.landid INNER JOIN tbl_wk_wedstrijd ON tbl_wk_landgroep.landgroepid=tbl_wk_wedstrijd.thuisploeg WHERE tbl_wk_land.landid=tbl_wk_landgroep.landid) AS thuisploeg, Unless I'm missing something in the table naming, this isn't going to be associated with the particular outer row that the select list is being run for and the where clause seems redundant with the join clause. Is that the intent? SELECT tbl_wk_wedstrijd.wedstrijdid, tbl_wk_wedstrijd.thuisploeg AS thuisploegid, tbl_wk_wedstrijd.uitploeg AS uitploegid, (SELECT tbl_wk_land.landnaam FROM tbl_wk_land INNER JOIN tbl_wk_landgroep ON tbl_wk_land.landid=tbl_wk_landgroep.landid INNER JOIN tbl_wk_wedstrijd ON tbl_wk_landgroep.landgroepid=tbl_wk_wedstrijd.thuisploeg WHERE tbl_wk_land.landid=tbl_wk_landgroep.landid) AS thuisploeg, (SELECT tbl_wk_land.landvlag FROM tbl_wk_land INNER JOIN tbl_wk_landgroep ON tbl_wk_land.landid=tbl_wk_landgroep.landid INNER JOIN tbl_wk_wedstrijd ON tbl_wk_landgroep.landgroepid=tbl_wk_wedstrijd.thuisploeg ) AS thuisploegvlag, (SELECT tbl_wk_land.landnaam FROM tbl_wk_land INNER JOIN tbl_wk_landgroep ON tbl_wk_land.landid=tbl_wk_landgroep.landid INNER JOIN tbl_wk_wedstrijd ON tbl_wk_landgroep.landgroepid=tbl_wk_wedstrijd.uitploeg ) AS uitploeg, (SELECT tbl_wk_land.landvlag FROM tbl_wk_land INNER JOIN tbl_wk_landgroep ON tbl_wk_land.landid=tbl_wk_landgroep.landid INNER JOIN tbl_wk_wedstrijd ON tbl_wk_landgroep.landgroepid=tbl_wk_wedstrijd.uitploeg ) AS uitploegvlag, tbl_wk_stadion.stadionplaats, tbl_wk_stadion.stadionnaam, tbl_wk_stadion.stadiongrootte, tbl_wk_stadion.stadionplaatje, tbl_wk_typewedstrijd.typewedstrijdnaam, tbl_wk_wedstrijd.datumentijd, tbl_wk_wedstrijd.omschrijving, tbl_wk_wedstrijd.uitslagthuis, tbl_wk_wedstrijd.uitslaguit, (SELECT tbl_wk_groep.groepnaam FROM tbl_wk_groep INNER JOIN tbl_wk_landgroep ON tbl_wk_groep.groepid=tbl_wk_landgroep.groepid INNER JOIN tbl_wk_wedstrijd ON tbl_wk_landgroep.landgroepid=tbl_wk_wedstrijd.thuisploeg ) AS groep FROM tbl_wk_wedstrijd INNER JOIN tbl_wk_stadion ON tbl_wk_wedstrijd.stadionid=tbl_wk_stadion.stadionid INNER JOIN tbl_wk_typewedstrijd ON tbl_wk_wedstrijd.typewedstrijdid=tbl_wk_typewedstrijd.typewedstrijdid; ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Adding ON UPDATE CASCADE to an existing foreign key
On Thu, 4 May 2006, Rich Doughty wrote: I have a foreign key constraint that I'd like to alter. I'd rather not drop and re-create it due to the size of the table involved. All I need to do is add an ON UPDATE CASCADE. Is it ok to set confupdtype to 'c' in pg_constraint (and will this be all that's needed) or is it safer to drop and recreate the constraint? I don't think that's going to work, you'd probably need to change the function associated with the trigger involved too. It's probably safer to do the drop and create. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] How to replace rows in table so that foreign key rows
On Thu, 20 Apr 2006, Stephan Szabo wrote: On Thu, 20 Apr 2006, Andrus wrote: I want to replace ( delete and insert) records in master table . I delete and insert record with same primary key. I want that foreign key records are not deleted. I tried begin; create temp table t1 ( pk integer primary key ); insert into t1 values(1); create temp table t2 (fk integer ); alter table t2 add foreign key (fk) references t1 on delete cascade deferrable initially deferred; insert into t2 values(1); -- Howto: set delete_constraint deferred delete from t1; insert into t1 values(1); commit; select * from t2; Observed: no rows Expected: t2 must contain one row. foreign key check and deletion should occur only when transaction commits. Actually, this looks like a case where SQL99 strongly implies that the action happens even for non-immediate constraints as part of the delete but SQL2003 changed that and we didn't notice. This should probably be reasonably straightforward to change I think (hope). Hmm, actually, it's a little less straightforward than I thought, mostly because I haven't seen something that seems to explicitly say what to do for non-immediate constraints that happened before the commit in the 2003 spec, I'd guess do the action at commit time as well, but the wording of the general rules talk about rows marked for deletion, but by the time of the commit, those rows are not marked for deletion any longer, but actually deleted as far as I can see and there doesn't appear (for non-match partial constraints) seem to be a special case for the referenced row coming back into existance as far as I can tell either. Any idea ? Is there any generic way to turn off foreign key constraints before delete command in transaction ? Right now, probably nothing short of dropping and readding the constraint. Or, if you're willing to patch, I think a first order approximation of what you want might be to remove the special cases in trigger.c (afterTriggerSetState) and tablecmds.c (createForeignKeyTriggers), but I haven't tested that. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] How to replace rows in table so that foreign key rows
On Fri, 21 Apr 2006, Andrus wrote: ... and there doesn't appear (for non-match partial constraints) seem to be a special case for the referenced row coming back into existance as far as I can tell either. Or, if you're willing to patch, I think a first order approximation of what you want might be to remove the special cases in trigger.c (afterTriggerSetState) and tablecmds.c (createForeignKeyTriggers), but I haven't tested that. Thank you. So I must create and maintain special version of PostgreSQL ? If the standard does say it should do what you want, it'll get changed for a later version, but probably not backpatched, so this would be a short term solution. The hardest part about changing it is making sure there aren't any new holes in the constraint. If the standard doesn't match what you want, then it's a bit more involved. Following the standard would still require you to maintain a special version for the rules you want or changing the expectation. Or, alternatively, you could make a case why the standard does say what you want (or allows what you want or is simply wrong) if there's a disagreement. ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] A few questions about ltree
On Fri, 21 Apr 2006, Alban Hertroys wrote:
Teodor Sigaev wrote:
Maybe something along the lines of the following is possible?:
Exact, it's for what ltree was developed.
Cool, looks like it is what I need then.
contrib_regression=# select 'a.b.c' @ 'a.b'::ltree;
?column?
--
t
(1 row)
How would you use this to constrain a foreign key?
We've been experimenting with a table containing a branch 'a', 'a.b' and
'a.b.c', but deleting 'a.b' didn't cause a constraint violation.
SQL CREATE TABLE ltree_test (path ltree PRIMARY KEY REFERENCES
ltree_test(path));
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index
ltree_test_pkey for table ltree_test
CREATE TABLE
SQL INSERT INTO ltree_test VALUES ('a'::ltree);
INSERT 84117368 1
SQL INSERT INTO ltree_test VALUES ('a.b'::ltree);
INSERT 84117369 1
SQL INSERT INTO ltree_test VALUES ('a.b.c'::ltree);
INSERT 84117370 1
SQL DELETE FROM ltree_test WHERE path = 'a.b'::ltree;
DELETE 1
I'm not sure why you expect this to error. Any row that would reference
a.b would be removed by the delete AFAICS.
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
Re: [GENERAL] A few questions about ltree
On Fri, 21 Apr 2006, Alban Hertroys wrote:
Stephan Szabo wrote:
SQL CREATE TABLE ltree_test (path ltree PRIMARY KEY REFERENCES
ltree_test(path));
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index
ltree_test_pkey for table ltree_test
CREATE TABLE
SQL INSERT INTO ltree_test VALUES ('a'::ltree);
INSERT 84117368 1
SQL INSERT INTO ltree_test VALUES ('a.b'::ltree);
INSERT 84117369 1
SQL INSERT INTO ltree_test VALUES ('a.b.c'::ltree);
INSERT 84117370 1
SQL DELETE FROM ltree_test WHERE path = 'a.b'::ltree;
DELETE 1
I'm not sure why you expect this to error. Any row that would reference
a.b would be removed by the delete AFAICS.
Nope, there's no ON DELETE CASCADE on the FK, and RESTRICT is the
default (thankfully).
The only row that matches 'a.b' that I see in the above is the second
insert which is also the row that is deleted in the delete. And since the
constraint uses equality, any row that matches path='a.b' is a target of
the delete because it's the same operator.
---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
Re: [GENERAL] How to replace rows in table so that foreign key rows
On Thu, 20 Apr 2006, Andrus wrote: I want to replace ( delete and insert) records in master table . I delete and insert record with same primary key. I want that foreign key records are not deleted. I tried begin; create temp table t1 ( pk integer primary key ); insert into t1 values(1); create temp table t2 (fk integer ); alter table t2 add foreign key (fk) references t1 on delete cascade deferrable initially deferred; insert into t2 values(1); -- Howto: set delete_constraint deferred delete from t1; insert into t1 values(1); commit; select * from t2; Observed: no rows Expected: t2 must contain one row. foreign key check and deletion should occur only when transaction commits. Actually, this looks like a case where SQL99 strongly implies that the action happens even for non-immediate constraints as part of the delete but SQL2003 changed that and we didn't notice. This should probably be reasonably straightforward to change I think (hope). Any idea ? Is there any generic way to turn off foreign key constraints before delete command in transaction ? Right now, probably nothing short of dropping and readding the constraint. ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Self-referencing and inherited table
On Tue, 4 Apr 2006, Anastasios Hatzis wrote:
Hello!
I want to realize some kind of parent-child relation with-in a table,
but have problems with foreign key / references. Probably this issue
occurs because I use inheritance (as implied by some pages, I found).
Probably. If the matching row was actually in Party or Organization, it
won't be considered as satisifying the constraint.
In other words, the following happens:
sszabo=# insert into SBObject values (1,now(), NULL);
INSERT 160212 1
sszabo=# insert into Party values (2,now(),1,'a');
INSERT 160213 1
sszabo=# insert into Party values (3,now(),2,'a');
ERROR: insert or update on table Party violates foreign key constraint
ownerOfObject
DETAIL: Key (objectOwner)=(2) is not present in table SBObject.
The first succeeds because the referenced row is in SBObject, the second
fails because it's in Party.
In the mailing list archive I couldn't find appropriate solutions, but
maybe I'm just blind and it's sooo easy. So I want to ask you, if you
can support me on this issue.
There's no easy solution, sadly. The best that I know of right now is
using an external table to store the keys and having all the various
tables in the hierarchy reference that. The schema below also doesn't
guarantee unique objectIDs so you may want to change it anyway (each table
is unique, but it's not guaranteed unique between tables if people insert
their own values rather than using the default).
-- after deleting the tables again
sszabo=# insert into SBObject values (1,now(), NULL);
INSERT 160216 1
sszabo=# insert into Party values (1,now(),1,'a');
INSERT 160217 1
Inheritance needs alot of work. (I really need a macro key on my keyboard
for that phrase).
ERROR: insert or update on table Organization violates foreign key
constraint ownerOfObject
DETAIL: Key (objectOwner)=(1) is not present in table SBObject.
Please note also, that the referenced row (here objectID = 1) was
existing at the moment of the insert statement. However, I wonder, why
in the DETAIL line, it says Key (objectOwner)=(1) ? Shouldn't it be
the local name (objectID)=(1)?
IIRC, the key shown is the one in the insert, so you know which columns of
the originally acted upon row were failing. The message is a bit wierd,
though, yeah.
CREATE TABLE SBObject
(
objectID int8 NOT NULL DEFAULT
nextval('SBObject_objectID_seq'::regclass),
createdOn timestamp NOT NULL DEFAULT now(),
objectOwner int8,
CONSTRAINT SBObject_pkey PRIMARY KEY (objectID)
) WITH OIDS;
ALTER TABLE SBObject OWNER TO myuser;
ALTER TABLE SBObject ADD CONSTRAINT ownerOfObject FOREIGN KEY
(objectOwner)
REFERENCES SBObject (objectID) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE CASCADE;
CREATE TABLE Party
(
objectID int8 NOT NULL DEFAULT
nextval('SBObject_objectID_seq'::regclass),
createdOn timestamp NOT NULL DEFAULT now(),
objectOwner int8,
autoName text,
CONSTRAINT Party_pkey PRIMARY KEY (objectID)
) INHERITS (SBObject)
WITH OIDS;
ALTER TABLE Party OWNER TO myuser;
ALTER TABLE Party ADD CONSTRAINT ownerOfObject FOREIGN KEY
(objectOwner)
REFERENCES SBObject (objectID) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE CASCADE;
CREATE TABLE Organization
(
objectID int8 NOT NULL DEFAULT
nextval('SBObject_objectID_seq'::regclass),
createdOn timestamp NOT NULL DEFAULT now(),
autoName text,
orgName text,
objectOwner int8,
CONSTRAINT Organization_pkey PRIMARY KEY (objectID)
) INHERITS (Party)
WITH OIDS;
ALTER TABLE Organization OWNER TO myuser;
ALTER TABLE Organization ADD CONSTRAINT ownerOfObject FOREIGN KEY
(objectOwner)
REFERENCES SBObject (objectID) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE CASCADE;
---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
Re: [GENERAL] How to use result column names in having cause
On Fri, 31 Mar 2006, Andrus wrote: In real application I have long expression instead of 123 and do'nt want repeat this expression in HAVING clause. You have to repeat the expression. AS changes the output name, it can't be used either in the where clause or any other limiting factor like 'having': Doc about HAVING condition says: Each column referenced in condition must unambiguously reference a grouping colum HAVING x AVG(bar) unambiguously references to a grouping column x IIRC technically the query is invalid, because group by isn't supposed to run on the output of select entries (as I think is stated by Each grouping column reference shall unambiguously reference a column of the table resulting from the from clause.) and I'd guess this is a side effect of allowing group by to work on the table resulting from the select list as well. I think the SQL way of writing this is to use a subselect and do two levels (ie, generate a subselect that gives the table you want to group and use it in the from clause of the outer query that does the grouping). ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] How to use viewsrules to dynamically choose which
On Sat, 1 Apr 2006, Ashley Moran wrote:
I'm still relatively new to Postgres (at least when it comes to
clever stuff - especially rules) so I hope I've missed something here.
Basically I'm still trying to combine multiple databases with
identical schemas into one schema, adding a column to each table to
indicate which schema it came from. (I'm prototyping an app in Ruby
on Rails so I want to have only one set of model classes, instead of
5). So I have views defined like this:
SELECT 'schema1'::varchar(10), * from schema1.table1
UNION ALL
SELECT 'schema2'::varchar(10), * from schema2.table1
etc...
These tables are all from a data feed we pay for, and is updated
nightly. It is separate from my application database.
Now, I want to take advantage of Rails' unit tests on these tables,
because I need to simulate changes in the data feed. So I thought
maybe I could add rules to the views, so Rails can load its test
fixtures into the model I defined and not realise it is feeding
multiple back-end tables.
This is my effort in a test database, so you can see what I'm trying
to do:
CREATE SCHEMA english;
CREATE TABLE english.names (
id serial NOT NULL PRIMARY KEY,
name character varying(50)
);
CREATE SCHEMA french;
CREATE TABLE french.names (
id serial NOT NULL PRIMARY KEY,
name character varying(50)
);
CREATE VIEW names AS
SELECT ('english'::character varying)::character varying(20)
AS language, * FROM english.names;
UNION ALL
SELECT ('french'::character varying)::character varying(20)
AS language, * FROM french.names;
CREATE RULE insert_english AS
ON INSERT TO names
WHERE (((new.language)::character varying(20))::text =
(('english'::character varying)::character varying
(20))::text)
DO INSTEAD INSERT INTO english.names (name) VALUES (new.name);
CREATE RULE insert_french AS
ON INSERT TO names
WHERE (((new.language)::character varying(20))::text =
(('french'::character varying)::character varying(20))::text)
DO INSTEAD INSERT INTO french.names (name) VALUES (new.name);
What should it do if you try to insert something that is neither french
nor english? I think an unconditional instead nothing rule might work
to supplement the two conditional ones if doing nothing is okay, but I
haven't tried.
(Please forgive any mistakes above - I cobbled it together from a
backup file)
Now if I some french names and some english names into the relvant
tables, the view works fine on SELECT, but on INSERT I get this error:
ERROR: cannot insert into a view
HINT: You need an unconditional ON INSERT DO INSTEAD rule.
Which suggests that what I want to do is impossible. Does anyone
know of a way to do this? If I can do it in the database I can
probably save hours of hacking the unit tests in Rails.
Thanks
Ashley
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
Re: [GENERAL] Foreign key / performance question
On Wed, 29 Mar 2006, Nico Callewaert wrote: Is it wise to define foreign keys for referential entegrity ? Example : I have a customer table with 40 fields. Out of that 40 fields, 10 fields contain information linked to other tables. So, is defining foreign keys for these 10 fields a good idea ? Because from what I understand, for every foreign key, there is an index defined. So, all these indexes has to be maintained. Is that killing performance ? What's the best practise : defining foreign keys or not ? The referencing side of the constraint doesn't need an index, although it's useful for speeding up deletes or updates to the referenced table (so, if those operations don't happen or are significantly rare, having those have to do a sequential scan may be better than the maintenance cost of the index on the referencing side). The referenced side does need an index, however that's theoretically the same index that's used to guarantee the required unique/primary key constraint. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] passing parameters to a trigger function
On Tue, 21 Mar 2006, Larry White wrote: I can't figure out how to pass parameters to a trigger function. I checked the documentation and saw that trigger functions don't take params in the usual fashion, but couldn't find an example of a pl-sql trigger function that used the original row data within the function. What I want is an on update trigger that creates an entry in a second table. The second (history) table has a subset of the columns in the first. Here's what I have so far: -- THIS IS WRONG - CANT PASS PARAMS INTO TRIGGER FUNCTION CREATE OR REPLACE FUNCTION audit_task (param type declarations were here) RETURNS TRIGGER AS ' -- create an audit trail record BEGIN -- Perform the insert INSERT INTO TASK_h (id, updated_by, updated, name, description ) VALUES ($1, $2, $3, $4, $5); RETURN NULL; END; ' LANGUAGE plpgsql; -- THE TRIGGER CREATE TRIGGER t_audit_task AFTER INSERT OR UPDATE ON task FOR EACH ROW EXECUTE PROCEDURE audit_task(); So the question is, how do I access the row from the original table so I can perform the insert? The old row is OLD and the new row NEW and do not need to be declared as arguments (in fact trigger functions are always currently created without declared arguments). I think section 36.10 in the 8.1 docs has info for other implicit arguments to plpgsql trigger functions. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Order of Update - Second Try
Ok, find attached a script called test.sql that will create three tables called parent, child, and totals. It will create a simple AFTER UPDATE trigger on child and a BEFORE trigger on parent simply to show that the values of batch and chkno are set to NULL right in the beginning. Just load the thing in with the \i command. There is a function created called myfunc(int). Simply do: SELECT myfunc(99); to see what happens. After you execute the function, you will find that parent.total is zero, child.apply_amt for each record is zero, but totals is still set to 1500. It should be 1000. Version info: rnd=# select version(); version -- PostgreSQL 7.4.6 on i686-redhat-linux-gnu, compiled by GCC gcc (GCC) 3.2.3 20030502 (Red Hat Linux 3.2.3-49) (1 row) Any insight is appreciated. I think in 8.0 or later it'd do what you want, however IIRC in 7.4 the after triggers are delayed until after the full execution of the function myfunc (ie, at the end of the outer statement). ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] can't create user collumn
On Fri, 17 Mar 2006, loki wrote: Hi, i'm just starting with postgres DB, but this looks very strange to me: If i try to create table with collumn user, it fails with error: create exec error:ERROR: syntax error at or near user at character 368 USER is a reserved word in SQL and as such cannot be used as a non-quoted identifier so user (with the quotes) should work, however you'd probably have to use the quotes in all cases. Technically, we allow some reserved words as non-quoted identifiers in some places, however to be compliant to spec you cannot use any of the reserved words that way. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] select where in and order
On Thu, 9 Mar 2006, Tony Smith wrote: I have two tables action and group: action id, name group: action_id rank I what to select from action table by order by the rank in the group table. If I use select * from action where id in (select action_id from group order by rank) The action may not be ordered by rank. How can I do it? Well, I think any answer is going to depend on a few pieces of information about the layout and desired behavior. Is group.action_id unique? If so, probably converting it into a join is easiest, I think that'd be something like: select action.* from action, group where action.id=group.action_id order by rank If not, which rank do you want to use from group for a matching id? You could probably then do something with group by and an aggregate. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Wish: remove ancient constructs from Postgres
On Mon, 27 Feb 2006, Nikolay Samokhvalov wrote: On 2/27/06, Bruno Wolff III [EMAIL PROTECTED] wrote: The alternatives to distinct on are painful. They are generally both harder to read and run slower. 'DISTINCT ON' is evil constuction, because (w/o any 'ORDER BY') it produses unpredictable result, as 'ORDER BY random()' does. And so does UNION in the standard under some circumstances (look at anywhere in the spec that a query expression is possibly non-deterministic), so I think that's a weak argument. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] Wish: remove ancient constructs from Postgres
On Mon, 27 Feb 2006, Nikolay Samokhvalov wrote: it's completely different thing. look at the spec and you'll understand the difference. in two words, with 'DISTINCT ON' we lose some values (from some columns), when UNION not (it just removes duplicates, comparing _entire_ rows). No it's not, really. Read the spec. The output of a union on a text field is non-deterministic (due to some collation choices). This means that the output of the query may be determined by an effectively random choice of which value to use. Basically AFAICT something like (modulo simple errors): select foo from ( select foo from tablea union select foo from tableb ) where foo = 'A' collate case_sensitive can give different results in the case of tablea having 'A' and tableb having 'a' if the union is using a case insensitive comparison. ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] Same data, different results in Postgres vs. FrontBase
On Sat, 18 Feb 2006, Brendan Duddridge wrote:
Hi,
I have a query that returns 569 rows in FrontBase, but only 30 rows
in Postgres. The data is the same as I just finished copying my
entire database over from FrontBase to Postgres.
I've reduced my problem to the following statement and have
discovered that FrontBase returns null rows along with the rows that
match the query and PostgreSQL only returns the not null rows.
CON.IS_SUBSCRIBED NOT IN ('X', 'P')
Is that normal?
Short form from the spec as we read it:
RVC NOT IN (IPV) = NOT (RVC IN (IPV)) = NOT (RVC =ANY IPV)
The result of RVC =ANY IPV can be described with:
If the implied comparison predicate [ RVC = IPVi] is true for at least
one row IPVi in IPV then true
If the implied comparison predicate is false for every row IPVi in IPV
then false
Otherwise unknown.
NULL = 'X' returns unknown, as does NULL = 'P', so the last case is the
one that should apply. NOT (unknown) is unknown, so the result of
CON.IS_SUBSCRIBED NOT IN ('X', 'P') is unknown for NULL IS_SUBSCRIBED.
Where clauses pass rows where the result of the clause is true, so those
rows are not part of the result.
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Domains
On Sat, 18 Feb 2006, Peter wrote: Hello, I am migrating to postgresql from another database. I want to take advantage of using domains. Let's suppose I create domain 'email'(varchar 128). Then I change my mind and want to increase all columnst that have type 'emaill' to varchar(255). How do I change the domain 'email' to the new datatype. I can not figure how to do it with alter domain syntax. It doesn't look like alter domain currenly has type changing support, so I don't think you can do this (in general) right now. Some conversions might be possible with direct alterations to system tables, but that's a bit dangerous. I don't know if anyone's working on this right now either, but it sounds reasonable (now that we have table column type changing). ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Why does an ON SELECT rule have to be named _RETURN?
On Mon, 13 Feb 2006, Ken Winter wrote: You're right: This thing I call a view-table would behave *exactly* like a view that has insert, update, and delete rules. The *only* difference I'm trying to achieve is to get it stored in pg_catalog.pg_class with relkind = 'r' (ordinary table) rather than 'v' (view). The problem is that you're not just punning the type to the client. You're punning the type to the server. Your view-table will be a table, even for operations that might not work because it's really a view, and the code isn't going to know to not allow it. If everything we had that works for ordinary tables worked for views, it wouldn't be a problem, but AFAIK that's not true. The *only* reason I'm trying to disguise a view as a table is to trick my client tools into letting me use their handy pre-made forms and grids to read and write to these structures. The reason I'm trying to activate these forms and grids is to enable my testing users to start entering and viewing test data immediately - without their having to learn and write SQL, and without my having to build data entry and review forms for them. I thought, all things considered, my little trick - admittedly a workaround - would be the easiest way to achieve what I need without requiring anything of either the PostgreSQL architects or the tool builders. So it is frustrating to be defeated by this one PostgreSQL constraint (which isn't even published in the documentation, as far as I can see). Well, it implies that create view and create table + create rule ... on select are equivalent, but I'd agree that this could probably be better documented. I just had another workaround idea - declare the view-table as an ordinary table and put triggers on it that implement the functionality of the rules - but before resorting to that I thought I'd try my simpler trick once more. So let me ask again: Is there any way to disable this constraint that forces the SELECT rule to be named _RETURN? Or is there any other way to accomplish what I'm trying to do? Apart from modifying the code, I don't think so. ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Why does an ON SELECT rule have to be named _RETURN?
On Sun, 12 Feb 2006, Ken Winter wrote: Hi Tom ~ You're right: I appealed to the PostgreSQL folks rather than the client tool builders. I did so because my guess is that the latter have a harder row to hoe: They have to figure out whether a view really IS updatable - most presumably aren't, so if they provide forms that offer to update views, most of the time these forms are going to crash. It seems harder for the client tool builders to figure out the updatability question than for PostgreSQL to let people (like me) do the real table with ON SELECT trick and take responsibility for making it work. I don't see why that is inherently broken. What does a real table with ON SELECT mean? For example, if a row is inserted that doesn't come into the on select output, was a row inserted? Can it cause unique key violations, can it satisfy a foreign key constraint? ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] Why does an ON SELECT rule have to be named _RETURN?
On Sun, 12 Feb 2006, Ken Winter wrote: -Original Message- From: Stephan Szabo [mailto:[EMAIL PROTECTED] Sent: Sunday, February 12, 2006 8:47 PM To: Ken Winter Cc: 'Tom Lane'; 'PostgreSQL pg-general List' Subject: Re: [GENERAL] Why does an ON SELECT rule have to be named _RETURN? On Sun, 12 Feb 2006, Ken Winter wrote: Hi Tom ~ You're right: I appealed to the PostgreSQL folks rather than the client tool builders. I did so because my guess is that the latter have a harder row to hoe: They have to figure out whether a view really IS updatable - most presumably aren't, so if they provide forms that offer to update views, most of the time these forms are going to crash. It seems harder for the client tool builders to figure out the updatability question than for PostgreSQL to let people (like me) do the real table with ON SELECT trick and take responsibility for making it work. I don't see why that is inherently broken. What does a real table with ON SELECT mean? It means a table that, due to the rules on it, works exactly like a view (from the client's perspective). (Here, let me call it a view-table.) No row ever gets inserted into the view-table. The rules deflect inserts into one or more base tables. Updates and deletes, though from the client's view they modify or remove rows in the view-table, actually update and delete in the underlying base tables. How is this different from a view with on insert, on update and on delete rules right now? For example, if a row is inserted that doesn't come into the on select output, was a row inserted? In what I'm doing, that would not happen. But there might be a case where someone would want a design where rows inserted through the view-table, though they do get inserted into the underlying base tables, would not be visible through SELECT actions on the view-table. I can't imagine offhand why anyone would want to do this, but I don't see why PostgreSQL should stop them. (...Actually, on second thought, I have thought of doing a trick like this myself, to get around the PostgreSQL constraint I'm complaining about: Define a view-table with all of the update rules on it, so no rows ever get inserted into it but my client tools can do updates against it; then define a second, read-only, view for SELECTs to reveal the data entered through the first view. Right; I would rather not stoop to this.) Can it cause unique key violations, can it satisfy a foreign key constraint? PK, UK, FK, and check constraints would all be defined on the base tables, not on the view-table. So actions on the view-table would satisfy or violate these constraints, like any other actions redirected through PostgreSQL update rules. But then this view-table isn't really a real table. If it's not a real table, it pretty much defeats the original stated argument of having real tables with on select rules. ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] referential integrity without trigger
On Thu, 9 Feb 2006, Alexander Presber wrote: Hello everybody, Assuming I want to empty and refill table A (with roughly the same content, preferrably in one transaction) and don't want to completely empty a dependent table B but still keep referential integrity after the commit. Without disabling A's on-delete-trigger B will be be emptied on commit, even when I inserted exactly the same data into A that I deleted an instant before. That is because the trigger gets called on commit, no matter if the deleted rows have reappeared. If I disable the trigger, My referential integrity is most likely corrupted. Is there a clever, general scheme to recheck and enforce foreign key contraints, after the responsible triggers have been disabled and reenabled? Probably the easiest way to do these things is to drop the constraint before, do stuff and re-add the constraint since that will check the constraint at the add constraint time. ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] question about MAKE_EXPIRED_TUPLES_VISIBLE
On Thu, 2 Feb 2006, Tony Caduto wrote: Tom Lane wrote: Tony Caduto [EMAIL PROTECTED] writes: I saw some where that if I recompiled my server with MAKE_EXPIRED_TUPLES_VISIBLE I would be able to see deleted rows? If you aren't a certified wizard you do NOT want to turn that on, because it will very probably help you make things worse. My opinion on it is on record: http://archives.postgresql.org/pgsql-patches/2005-02/msg00126.php Well, it does not seem to do anything, I enabled it with ./configure --enable-MAKE_EXPIRED_TUPLES_VISIBLE I then thought well maybe I need to do dump of the table and the deleted tuples would be in there, but no. ./configure --enable-MAKE_EXPIRED_TUPLES_VISIBLE is the correct way to enable it right? I think something like CFLAGS=-D MAKE_EXPIRED_TUPLES_VISIBLE ./configure would be the way to get it. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
