Re: [GENERAL] using pg's internal timezone database?
On Tue, Dec 20, 2011 at 05:29:15AM -0700, Scott Marlowe wrote: On Tue, Dec 20, 2011 at 2:05 AM, Louis-David Mitterrand vindex+lists-pgsql-gene...@apartia.org wrote: Hi, To provide my forum users with a 'timezeone' preference in their profile how can I use postgresql's internal table of timezones ? I found a reference to it here: http://www.postgresql.org/docs/7.2/static/timezones.html but not in recent versions docs. You can view the pg internal tz database by looking at what's in the tables pg_timezone_names and pg_timezone_abbrevs Hi, Thanks for that info. There are 1146 timezones listed in that table! Which sublist is the most commonly used or standard? posix/* ? -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] using pg's internal timezone database?
Hi, To provide my forum users with a 'timezeone' preference in their profile how can I use postgresql's internal table of timezones ? I found a reference to it here: http://www.postgresql.org/docs/7.2/static/timezones.html but not in recent versions docs. Thanks, -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] FK violation on (emtpy) parent table
Hi, I have an empty parent 'price' table with several partitioned child tables that contain the actual data. How can I reference the parent 'price' table in a FK? When I try I get a FK violation. Is that expected behavior? Is there another way to do it? Thanks, -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] EXECUTE ... into var doesn't set FOUND: bug or feature?
Hi, I noticed that in a pl/pgsql function FOUND is not set after an EXECUTE ... into var; Bug or feature? -- 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] EXECUTE ... into var doesn't set FOUND: bug or feature?
On Thu, Sep 16, 2010 at 10:12:57AM -0400, Tom Lane wrote: Louis-David Mitterrand vindex+lists-pgsql-gene...@apartia.org writes: I noticed that in a pl/pgsql function FOUND is not set after an EXECUTE ... into var; Bug or feature? It's behaving as documented: http://www.postgresql.org/docs/9.0/static/plpgsql-statements.html#PLPGSQL-STATEMENTS-DIAGNOSTICS Adding EXECUTE to the list of statements that change FOUND would have a rather large risk of breaking existing plpgsql functions, IMO. but is really surprising and non-intuitive, given that 'return query execute' does. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] inconsistency in aliasing
Hi, This works: critik=# select current_timestamp::abstime::int4 as score order by score; This doesn't: critik=# select current_timestamp::abstime::int4 as score order by score + 1; ERROR: column score does not exist LINE 1: ...urrent_timestamp::abstime::int4 as score order by score + 1 ... Any idea ? Thanks, -- http://www.critikart.net -- 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] inconsistency in aliasing
On Wed, Jan 14, 2009 at 06:31:55PM +0700, dbalinglung wrote: what for of the syntax command +1 on order by ? maybe just wrong to given result about the error query on order by, it's BUG ? *PARSE ERROR* -- 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] inconsistency in aliasing
On Wed, Jan 14, 2009 at 12:06:47PM +0100, A. Kretschmer wrote: In response to Louis-David Mitterrand : Hi, This works: critik=# select current_timestamp::abstime::int4 as score order by score; This doesn't: critik=# select current_timestamp::abstime::int4 as score order by score + 1; ERROR: column score does not exist LINE 1: ...urrent_timestamp::abstime::int4 as score order by score + 1 ... Any idea ? Yes, you can't use the alias in the ORDER BY. Use the real column-name. select current_timestamp::abstime::int4 as score order by current_timestamp::abstime::int4; Did you try select current_timestamp::abstime::int4 as score order by score; ? This seems to be an order by alias -- http://www.critikart.net -- 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] inconsistency in aliasing
On Wed, Jan 14, 2009 at 05:53:57AM -0800, Lennin Caro wrote: --- On Wed, 1/14/09, Louis-David Mitterrand vindex+lists-pgsql-gene...@apartia.org wrote: From: Louis-David Mitterrand vindex+lists-pgsql-gene...@apartia.org Subject: Re: [GENERAL] inconsistency in aliasing To: pgsql-general@postgresql.org Date: Wednesday, January 14, 2009, 11:27 AM On Wed, Jan 14, 2009 at 12:06:47PM +0100, A. Kretschmer wrote: In response to Louis-David Mitterrand : Hi, This works: critik=# select current_timestamp::abstime::int4 as score order by score; This doesn't: critik=# select current_timestamp::abstime::int4 as score order by score + 1; ERROR: column score does not exist LINE 1: ...urrent_timestamp::abstime::int4 as score order by score + 1 ... Any idea ? Yes, you can't use the alias in the ORDER BY. Use the real column-name. select current_timestamp::abstime::int4 as score order by current_timestamp::abstime::int4; Did you try select current_timestamp::abstime::int4 as score order by score; ? This seems to be an order by alias -- http://www.critikart.net you can't use operator in the group by, try this Really? select current_timestamp::abstime::int4 as score order by 1 + 1 ; score 1231941662 (1 row) -- http://www.critikart.net -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] DBI error when changing views
Hello, When changing a view in my mod_perl (mason) application I typically get this error if I don't restart apache: DBD::Pg::st execute failed: ERROR: cached plan must not change result type Is there a way to avoid having to restart apache? Thanks, -- http://www.critikart.net -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] group by error message?
Hi, Running this query: critik=# select distinct pt.type_fr,sum(e2.id_event) from person_type pt natural join person_to_event join event e using (id_event) LEFT JOIN event e2 ON e.id_event = e2.id_event AND e2.id_event=219 join event_type et ON e.id_event_type = et.id_event_type where et.type_fr='théâtre' GROUP BY pt.type_fr IS NULL; gives this error: ERROR: column pt.type_fr must appear in the GROUP BY clause or be used in an aggregate function But pt.type_fr is _already_ in the group by. When adding e2.id_event however (GROUP BY pt.type_fr,e2.id_event) it works. Did I miss something 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] group by error message?
On Thu, Sep 25, 2008 at 11:01:08AM -0400, Tom Lane wrote: Louis-David Mitterrand [EMAIL PROTECTED] writes: critik=# select distinct pt.type_fr,sum(e2.id_event) from person_type pt natural join person_to_event join event e using (id_event) LEFT JOIN event e2 ON e.id_event = e2.id_event AND e2.id_event=219 join event_type et ON e.id_event_type = et.id_event_type where et.type_fr='théâtre' GROUP BY pt.type_fr IS NULL; gives this error: ERROR: column pt.type_fr must appear in the GROUP BY clause or be used in an aggregate function But pt.type_fr is _already_ in the group by. No, pt.type_fr IS NULL is in the group by. That doesn't ensure that each group has a unique value of pt.type_fr itself. Oops, once more I jumped the gun a bit too fast. Thanks for catching it Tom! Best regards, -- 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] can't create index with 'dowcast' row
On Fri, Jan 25, 2008 at 12:17:16AM -0500, Tom Lane wrote: Louis-David Mitterrand [EMAIL PROTECTED] writes: CREATE UNIQUE INDEX visit_idx ON visit_buffer USING btree (id_session, id_story, created_on::date); psql:visit_pkey.sql:5: ERROR: syntax error at or near :: The reason that didn't work is that you need parentheses around an index expression (otherwise the CREATE INDEX syntax would be ambiguous). This worked fine once I changed the type to a simple 'timestamp'. CREATE UNIQUE INDEX visit_idx ON visit_buffer USING btree (id_session, id_story, extract(date from created_on)); psql:visit_pkey.sql:4: ERROR: functions in index expression must be marked IMMUTABLE I take it created_on is timestamp with time zone, not plain timestamp? The problem here is that the coercion to date is not immutable because it depends on the timezone setting. (The other way would have failed too, once you got past the syntax detail.) You need to figure out what your intended semantics are --- in particular, whose idea of midnight should divide one day from the next --- and then use a unique index on something like ((created_on AT TIME ZONE 'Europe/Paris')::date) Note that the nearby recommendation to override the immutability test with a phonily-immutable wrapper function would be a real bad idea, because such an index would misbehave anytime someone changed their timezone setting. Thanks Tom for that explanation. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[GENERAL] can't create index with 'dowcast' row
Hi, To constraint unique'ness of my visitors to a 24h periode I tried created a index including the 'date' part of the created_on timestamp: CREATE UNIQUE INDEX visit_idx ON visit_buffer USING btree (id_session, id_story, created_on::date); psql:visit_pkey.sql:5: ERROR: syntax error at or near :: LINE 1: ...buffer USING btree (id_session, id_story, created_on::date); and this: CREATE UNIQUE INDEX visit_idx ON visit_buffer USING btree (id_session, id_story, extract(date from created_on)); psql:visit_pkey.sql:4: ERROR: functions in index expression must be marked IMMUTABLE How can I achieve what I am trying? Thanks, ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] postgres UTC different from perl?
On Wed, Dec 19, 2007 at 08:14:17PM -0500, Tom Lane wrote:
Richard Huxton [EMAIL PROTECTED] writes:
I'm not sure that (CURRENT_DATE AT TIME ZONE 'UTC') does what you think
it does. Try setting your timezone to various offsets and exploring.
In fact, I think it's adjusting in exactly the wrong direction.
I get the right number from
regression=# select date_part('epoch', 'today'::timestamp at time zone 'UTC');
date_part
1198022400
(1 row)
and the wrong one from
regression=# select date_part('epoch', 'today'::timestamptz at time zone
'UTC');
date_part
1198058400
(1 row)
and I think the locution with CURRENT_DATE is equivalent to the second
case because timestamptz is the preferred type to promote date to.
Does that mean it's a postgresql bug?
---(end of broadcast)---
TIP 6: explain analyze is your friend
[GENERAL] postgres UTC different from perl?
Hi,
when trying:
psql template1 -c select date_part('epoch',current_date at time zone 'UTC');
date_part
1198015200
the result is different from
perl -MDateTime -le 'print DateTime-today(time_zone = UTC)-epoch;'
1198022400
Is there an issue with postgresql?
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
[GENERAL] query pegs beta4
This new query of mine pegs beta4, it doesn't return and CPU is at 100%: select l.id_location,l.name, a.city from location l, address a, show_date x, show s, show s2 where (l.id_address = a.id_address and x.id_location = l.id_location and s.id_show = x.id_show and s2.show_type = s.show_type and s2.id_show = 305) or l.id_location = 172; The tables are not big, at most a few hundred elements each, if that. Maybe the query itself is flawed, I haven't tried it on other versions of postgres. ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] query pegs beta4
On Sat, Dec 15, 2007 at 12:39:30PM -0500, Tom Lane wrote: Louis-David Mitterrand [EMAIL PROTECTED] writes: This new query of mine pegs beta4, it doesn't return and CPU is at 100%: select l.id_location,l.name, a.city from location l, address a, show_date x, show s, show s2 where (l.id_address = a.id_address and x.id_location = l.id_location and s.id_show = x.id_show and s2.show_type = s.show_type and s2.id_show = 305) or l.id_location = 172; The tables are not big, at most a few hundred elements each, if that. Maybe the query itself is flawed, I'd say so. Any l row with id_location = 172 joins to the cartesian product of all the other tables. I doubt that's what you meant. Hi Tom, No, what I really meant (and clumsily attempted here) is: either return the list of locations that have been already used for the same 'show_type' as the current show) OR just return the newly created location 172. I just backtracked and expressed the equivalent in perl, so no problem here. Thanks, ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
[GENERAL] 8.3beta4 needs a dump/restore?
Hi, While upgrading from 8.3-beta3 to beta4, postgres complained that the database format was not supported. I had to restore from backup. Was that intended? I didn't see any beta4 announcement on -general or -hackers. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] timestamp skew during 7.4 - 8.2 upgrade
On Fri, Aug 10, 2007 at 04:59:52PM -0400, Tom Lane wrote: Karsten Hilbert [EMAIL PROTECTED] writes: On Fri, Aug 10, 2007 at 10:11:29AM +0200, Louis-David Mitterrand wrote: So if I understand correctly, a timestamp_tz is ... ... stored as UTC in the backend ... sent to clients shifted by whatever timezone was requested by the client by one of several mechanisms: - set timezone to ... used by the client - select ... at time zone ... used by the client - the server timezone if neither of the above is used The other point to be clear on is that the shifting is done according to whatever timezone rule files the server currently has. Since politicians keep changing daylight-savings rules, the same UTC date/time might be displayed differently after an update of the relevant rule file. (I am located in Paris, GMT+2, using debian unstable) When using date here is the output on the server where the postgresql upgrade (or more likely that's server's subsequent misconfiguration) changed our timestamps: uruk:~# date Sat Aug 11 10:50:46 CEST 2007 uruk:~# date --utc Sat Aug 11 08:50:49 UTC 2007 uruk:~# and: uruk:~# tzconfig Your current time zone is set to Europe/Paris But, I found something fishy that particular server: uruk:~# hwclock Sat 11 Aug 2007 10:47:36 AM CEST -0.630123 seconds uruk:~# hwclock --utc Sat 11 Aug 2007 12:47:39 PM CEST -0.600430 seconds Whereas on my other servers hwclock --utc displays the same time (is that normal?): zenon:~# hwclock Sat 11 Aug 2007 10:50:21 AM CEST -0.015345 seconds zenon:~# hwclock --utc Sat 11 Aug 2007 10:50:24 AM CEST -0.000235 seconds Is postgres using the same time reference as hwclock or date ? Thanks, ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] timestamp skew during 7.4 - 8.2 upgrade
On Thu, Aug 09, 2007 at 10:49:38AM -0500, Scott Marlowe wrote: On 8/9/07, Louis-David Mitterrand [EMAIL PROTECTED] wrote: Hi, After our 7.4 to 8.2 upgrade using debian tools, we realized that some of our timestamps with tz had shifted: For example '2007-04-01 00:00:00+02' became '2007-03-31 23:00:00+01' which is on a different month. Some of our applications were severely disturbed by that. Has anyone noticed that? Is there a way that would could have avoided it? Since timestamptz is stored as a GMT time, and then an offset is applied on retrieval, I'd guess that with 8.2 you're using up to date timezone files, and with 7.4 they were out of date and therefore returning the wrong time. I.e. they had the wrong offset for a given date. Not sure how you could avoid it off the top of my head, besides keeping your 7.4 db tz data up to date. I sheepishly admit I never really understood the timestamp_tz mechanism in postgres, until that issue reared its head. So if I understand correctly, a timestamp_tz is UTC time shifted according to the host's timezone configuration? For example if I travel with my server and cross several timezones, my timestamp_tz's will display a different time (provided I run the tzselect utility in Linux) ? Thanks, ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[GENERAL] timestamp skew during 7.4 - 8.2 upgrade
Hi, After our 7.4 to 8.2 upgrade using debian tools, we realized that some of our timestamps with tz had shifted: For example '2007-04-01 00:00:00+02' became '2007-03-31 23:00:00+01' which is on a different month. Some of our applications were severely disturbed by that. Has anyone noticed that? Is there a way that would could have avoided it? Thanks, ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] CASE in ORDER BY clause
On Fri, Jul 06, 2007 at 08:02:54PM +0400, Viatcheslav Kalinin wrote: Louis-David Mitterrand wrote: # select start_date from show_date # order by # case when start_date CURRENT_DATE then start_date end desc, # case when start_date = CURRENT_DATE then start_date end asc; But... this works! Many thanks, ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] CASE in ORDER BY clause
On Fri, Jul 06, 2007 at 08:02:54PM +0400, Viatcheslav Kalinin wrote: # select start_date from show_date # order by # case when start_date CURRENT_DATE then start_date end desc, # case when start_date = CURRENT_DATE then start_date end asc; The strange thing is when I try: select start_date from show_date order by case when start_date CURRENT_DATE then 'start_date asc' else 'start_date desc ' end; It lists start_date's without ordering them (does nothing). However if I try: select start_date from show_date order by 'start_date desc'; I get a: ERROR: non-integer constant in ORDER BY Bug? Inconsistency? ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
[GENERAL] CASE in ORDER BY clause
Hi, I am trying the following: critik=# select start_date from show_date order by case when start_date CURRENT_DATE then start_date desc else start_date asc end; ERROR: syntax error at or near desc LINE 1: ...se when start_date CURRENT_DATE then start_date desc else ... If I remove the desc and asc then the quey is accepted but doesn't do what I want. OTOH if I try: critik=# select start_date from show_date order by start_date case when start_date CURRENT_DATE then desc else asc end; ERROR: syntax error at or near case LINE 1: ...ect start_date from show_date order by start_date case when ... How can i order ASC or DESC depending on a condition? Thanks, ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[GENERAL] table referencing several others
Hello, To support a forum application I have a forum table: Column|Type -+- created_by | integer created_on | timestamp without time zone modified_by | integer modified_on | timestamp without time zone id_forum| integer id_parent | integer subject | text message | text id_show | integer id_thread | integer id_person | integer The id_show and id_person columns are meant to point to a show or person object about which the specific forum thread is started (threads can also have a free subject and these fields are then empty). Now I'd like to add forums about locations (theatres, museums, etc.) so I am thinking about adding an id_location column, but this method seems a bit inelegant to me: - every subsequent message of the thread will carry these useless fields (only the first message in thread uses one of them), - if I want to connect a forum to a new type of object, yet a new id_object will be required, Is there a better way to have a single field that can relate with several objects and still benefit from integrity checks? Thanks, ---(end of broadcast)--- TIP 6: explain analyze is your friend
[GENERAL] typical schema for a forum?
Hi, I'm trying to implement a forum with mason and postgresql. What is the typical database schema of a forum (threaded or flat) application? Thanks, ---(end of broadcast)--- TIP 6: explain analyze is your friend
[GENERAL] DBI support for pg native arrays?
Hello,
For a select array(...) as col1, col2, col3 from table I'd like the DBI
driver to output col1 as a perl array instead of a scalar
{res1,res2,etc.} representation of it.
Is that somehow possible? I looked at the docs without finding anything.
Thanks,
---(end of broadcast)---
TIP 6: explain analyze is your friend
[GENERAL] upgrading pl/pgsql triggers from 7.4 to 8.2
Hello, We tried upgrading a 7.4 base to 8.2 and found many issues with the triggers. What are the main changes in the pl/pgsql syntax or contraints checking between these two version? Thanks, ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[GENERAL] deduce sequence name from table and column
Hi,
Is there a way (from DBI) to deduce a sequence name from the table and
column it is attached to?
For instance:
Column| Type |Modifiers
-+-+--
id_fonction | integer | not null default
nextval(('fonction_id_fonction_seq'::text)::regclass)
I'd like to be able to programmatically find fonction_id_fonction_seq
so that I can then call nextval() on it.
Thanks,
--
Only half the people in the world are above average intelligence.
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] deduce sequence name from table and column
On Tue, Dec 20, 2005 at 10:31:46AM -0500, Tom Lane wrote: Louis-David Mitterrand [EMAIL PROTECTED] writes: Is there a way (from DBI) to deduce a sequence name from the table and column it is attached to? Since 8.0 you can use pg_get_serial_sequence(), see http://www.postgresql.org/docs/8.0/static/functions-info.html Just what I needed, Thanks! -- -= this .sig is not funny =- ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[GENERAL] last comma inside CREATE TABLE () statements
Is it against the SQL standard to accept a trailing comma in a table declaration? CREATE TABLE "currency" ( currency_id varchar(3), rate float, BOOM! parse error ); As in perl, it would make life easier to simply ignore/accept a trailing comma on table declarations. -- PHEDRE: Insense, o suis-je ? et qu'ai-je dit ? O laiss-je garer mes voeux et mon esprit ? (Phdre, J-B Racine, acte 1, scne 3) ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[GENERAL] Re: Trusted plperl
On Fri, Apr 20, 2001 at 03:42:24PM -0400, [EMAIL PROTECTED] wrote: Hey folks, I sent out this question a while back without ever getting an answer, so here I go again :) Has anyone managed to compile a trusted plperl interpreter into postgres? The Opcode stuff which blocks the use of external modules, and 99% of perl's built-in operators really bugs me :( Mark really has a point there. Why not simply allow access to full perl functionality to postgres superusers (as with C functions)? A recent example on pgsql-general has shown that a 15-line pl/pgsql script can be replaced by a one-line perl expression. If perl could reach the same level of integration with Pg that mod_perl has with Apache: full access to the SPI, no restrictions on what can be done, it would really help us make a quantum leap in productivity. Programming in pl/pgsql is nice and all, maybe mostly for oracle refugees, but for most uses it's ridiculously limited and its syntax reminds of BASIC. It's as far as a modern programming (or scripting) language as can be. Again creating pl/pgsql was wonderful and indispensable for oracle migration, but maybe it's the time to give Pg its swiss-army chainsaw with PERL! When one considers the power of mod_perl programming, the prospect of that functionality inside Pg is awe-inspiring. As a first, immediate step, please free pgperl from its sanbox. -- ARICIE: Phèdre en vain s'honorait des soupirs de Thésée : Pour moi, je suis plus fière, et fuis la gloire; aisée D'arracher un hommage à mille autres offert, Et d'entrer dans un coeur de toutes parts ouvert. (Phèdre, J-B Racine, acte 2, scène 1) ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
[GENERAL] Re: avoiding endless loop in an UPDATE trigger
On Mon, Feb 26, 2001 at 11:53:51AM -0800, Norman J. Clarke wrote: Hello, I am attempting to write a trigger function or rule in pl/pgsql that runs on UPDATE to a table named "nodes". From inside this trigger, I would like to UPDATE the same "nodes" table. How can I do this without entering into an endless loop? What I did is add a no_update bool to the table and test: if new.no_update = true then new.no_update = false; else do your update here new.no_update = true; endif -- Jesus is coming! Everyone look busy! ---(end of broadcast)--- TIP 3: 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
[GENERAL] UNIQUE constraint fails
With 7.1b4:
test=# create table auction_type(id serial,login text,birthday timestamp);
test=# create table auction(unique(login)) inherits("auction_type");
ERROR: inherited attribute "login" cannot be a PRIMARY KEY because it is not marked
NOT NULL
But I didn't ask that "login" be a PRIMARY KEY, only a UNIQUE. What's
going on there?
Cheers,
--
ARICIE: Vous laissez dans l'erreur un père qui vous aime ?
Cruel, si de mes pleurs méprisant le pouvoir,
Vous consentez sans peine à ne plus me revoir,
Partez, séparez-vous de la triste Aricie.
(Phèdre, J-B Racine, acte 5, scène 1)
[GENERAL] inconstistent inheritance in 7.1?
Why does a SELECT apply to all tables, including inherited ones and not UPDATEs or DELETEs? Is there a way to UPDATE a whole table hierarchy in one fell swoop? TIA -- HIPPOLYTE: Donnerai-je l'exemple à la témérité ? Et dans un fol amour ma jeunesse embarquée... (Phèdre, J-B Racine, acte 1, scène 1)
[GENERAL] Re: inconstistent inheritance in 7.1?
On Thu, Feb 22, 2001 at 02:45:46PM +0100, Louis-David Mitterrand wrote: Why does a SELECT apply to all tables, including inherited ones and not UPDATEs or DELETEs? Is there a way to UPDATE a whole table hierarchy in one fell swoop? Oops, I take that back; indeed it UPDATEs and DELETEs apply to inherited tables, however triggers don't. -- OENONE: De quel droit sur vous-même osez-vous attenter ? Vous offensez les Dieux auteurs de votre vie ; (Phèdre, J-B Racine, acte 1, scène 3)
[GENERAL] iterating over all NEW.* values in a trigger?
Inside a plpgsql function trigger, is it possible to a loop over all fields of the NEW record (and inspect their value) without knowing in advance from which table NEW will come? I am trying the following: DROP FUNCTION arch_func(); CREATE FUNCTION arch_func() RETURNS opaque AS ' DECLARE rec ecord; BEGIN FOR rec IN SELECT a.attname,t.typname FROM pg_attribute a JOIN pg_class c ON (a.attrelid = c.oid AND c.relname = TG_RELNAME) JOIN pg_type t ON (t.oid = a.atttypid) LOOP RAISE NOTICE ''Hello %: %!'', rec.attname, rec.typname; -- -- here I would like to access NEW.rec.attname in order to be able -- to: -- 1) compare it to OLD.rec.attname, -- 2) check if there is a change, -- 3) save a changed value in another "archive" table -- END LOOP; RETURN NULL; END; ' LANGUAGE 'plpgsql'; DROP TRIGGER arch_after ON auction; CREATE TRIGGER arch_after AFTER UPDATE OR DELETE ON auction FOR each ROW EXECUTE PROCEDURE arch_func(); -- PHEDRE: Dans mes jaloux transports je le veux implorer. Que fais-je ? Où ma raison va-t-elle s'égarer ? (Phèdre, J-B Racine, acte 4, scène 6)
[GENERAL] strategies for keeping an audit trail of UPDATEs
Hello, In our app we must keep a trace of all changes (UPDATEs) done to an important_table, so that it's possible to get a snapshot of a given record at a given date. The implementation strategy we are thinking about: 1. create an important_table_archive which inherits from important_table, 2. create a trigger ON UPDATE of important_table which automatically creates a record in important_table_archive containing only the UPDATEd fields on the original record along with the modification date and author and the primary key, Is this a viable strategy for that kind of requirement? Is there a better, more orthodox one? Thanks in advance, -- PANOPE: Déjà même Hippolyte est tout prêt à partir ; Et l'on craint, s'il paraît dans ce nouvel orage, Qu'il n'entraîne après lui tout un peuple volage. (Phèdre, J-B Racine, acte 1, scène 4)
[GENERAL] using tables as types in other tables
In the app we are developing the concept of an address will occur very
often on many tables (vendor, clients, employees, etc.) so we are
looking to avoid code duplication by centralizing the addresses in one
table. However I once read on one of the pgsql- lists that one could use
a table name as a type:
create table address(street text, zip text, city text, country text);
create table employee(emp_addr address, emp_id int);
This is accepted by Postgres but the question is how to insert into the
employee table? What syntax should be used? I tried:
insert into employee values (('mystreet','myzip','mycity','mycountry'), 1);
But it doesn't work.
Should I proceed along that path or would I be better off using a
central address table with keys to the other tables?
Thanks in advance for your insight,
--
THESEE: D'un perfide ennemi j'ai purgé la nature ;
A ses monstres lui-même a servi de pâture ;
(Phèdre, J-B Racine, acte 3, scène 5)
[GENERAL] a web interface to visualize tables
Hello, I need a tool to interactively visualize (not administer) DB tables from a web interface. Ideally this tool would let me: - rename column headers, - set cell alignments, widths, background colors, - reorder columns, - save all these visualisation settings in a DB, - it would be written in perl (even better: mod_perl), - uses the DBI interface, Does such a beast exist? I am in the process of writing one, so I thought I'd check first... Thanks in advance, -- Louis-David Mitterrand - [EMAIL PROTECTED] - http://www.apartia.org "Kill a man, and you are an assassin. Kill millions of men, and you are a conqueror. Kill everyone, and you are a god." -- Jean Rostand
[GENERAL] detecting NULL column in SPI function
Hello, In a SPI function I have this code snippet: double startprice = *DatumGetFloat64(SPI_getbinval( auction_tt-vals[i], auction_tt-tupdesc, SPI_fnumber(auction_tt-tupdesc, "startprice"), isnull)); To extract a value from the startprice column. It only works if "startprice" is not NULL, otherwise the backend crashes. In the provided example SPI functions I couldn't determine a good way of testing for NULL on columns. Should I first try to return the Datum, test it for NULL, then run DatumGetFloat64() on it? Isn't there a simpler way? Thanks in advance for your help, cheers, -- Louis-David Mitterrand - [EMAIL PROTECTED] - http://www.apartia.org Hand, n.: A singular instrument worn at the end of a human arm and commonly thrust into somebody's pocket. -- Ambrose Bierce, "The Devil's Dictionary"
[GENERAL] running maintenance tasks on DB
Hello, Approaching completion of an online auction system based on Postgres and mod_perl/Apache I yet have to devise a good way of running certain maintenance tasks on the DB like closing auctions, notifying winners, transferring old records to archive tables, etc. What is the usual way of programming such tasks? Write a backend function (in pl/sql or C) and call it from a cron job? From a mod_perl handler? Thanks in advance for any insight, cheers, -- Louis-David Mitterrand - [EMAIL PROTECTED] - http://www.apartia.org
[GENERAL] OID decreasing?
Hello, In this table one can see that OID 178502 was created after OID 178593. Is this normal behaviour? I thought that OID sequences kept going up. oid | login | price |created +---+---+ 178383 | vindex| 32500 | 2000-08-14 16:21:04+02 178384 | cunctator | 33000 | 2000-08-17 09:03:17+02 178432 | papy | 33500 | 2000-08-17 09:15:15+02 178465 | vindex| 34000 | 2000-08-17 09:15:59+02 178496 | papy | 34500 | 2000-08-17 09:21:33+02 178497 | vindex| 35000 | 2000-08-17 09:21:33+02 178499 | papy | 65000 | 2000-08-17 12:27:09+02 178501 | vindex| 75000 | 2000-08-17 12:27:09+02 178502 | papy | 77500 | 2000-08-17 12:27:09+02 178528 | papy | 35500 | 2000-08-17 12:26:02+02 178529 | vindex| 36000 | 2000-08-17 12:26:02+02 178561 | papy | 55000 | 2000-08-17 12:26:25+02 178562 | vindex| 57500 | 2000-08-17 12:26:25+02 178592 | papy | 6 | 2000-08-17 12:26:52+02 178593 | vindex| 62500 | 2000-08-17 12:26:52+02 -- Louis-David Mitterrand - [EMAIL PROTECTED] - http://www.apartia.org Linux: The Ultimate NT Service Pack
[GENERAL] dangers of setlocale() in backend (was: problem with float8 input format)
On Sat, Aug 12, 2000 at 12:15:26PM -0400, Tom Lane wrote: Louis-David Mitterrand [EMAIL PROTECTED] writes: When "seller_locale" is, for instance, "de_DE", then I get theses errors: ERROR: Bad float8 input format '0.05' Is Postgres expecting the float as 0,05 (notice the comma) because of the locale? I'm sure that's the issue. If you look at the source of the message (float8in() in src/backend/utils/adt/float.c) you'll see that it's just relying on strtod() to parse the input. If your local strtod() is locale-sensitive then the expected input format changes accordingly. Not sure whether that's a feature or a bug, but it's how Postgres has always worked. So using "setlocale(LC_MESSAGES, seller_locale)" instead of "LC_ALL" should be safe? It doesn't touch numeric formatting. IMPORTANT: changing the backend's locale on-the-fly is an EXTREMELY DANGEROUS thing to do, and I strongly recommend that you find another way to solve your problem. The "problem" I am trying to solve is to send e-mail notifications to auction bidders in their own language with the proper number formatting, etc. From what you are saying I'll probably have to move these notifications to the mod_perl layer of the application. Too bad... not being a C programmer it took me a while to be able to send mail from the trigger. Oh well. Running with a different locale changes the expected sort order for indices, which means that your indices will become corrupted as items get inserted out of order compared to other items (for one definition of "order" or the other), leading to failure to find items that should be found in later searches. You mean the indices change because accented characters can come into play w.r.t the sort order? Given that your trigger has been exiting with the changed locale still in force, I'm surprised your DB is still functional at all (perhaps you have no indexes on textual columns?). Right, not yet. But it'd be extremely dangerous even if you were to restore the old setting before exit --- what happens if there's an elog(ERROR) before you can restore? At present, the only safe way to handle locale is to set it in the postmaster's environment, never in individual backends. What's more, you'd better be careful that the postmaster is always started with the same locale setting for a given database. You can find instances of people being burnt by this sort of problem in the archives :-( Many thanks for the thorough and clear explanation of the issues. Cheers, [much relieved at having found "why"] -- Louis-David Mitterrand - [EMAIL PROTECTED] - http://www.apartia.org "Of course Australia was marked for glory, for its people had been chosen by the finest judges in England."
[GENERAL] problem with float8 input format
Hello, Suddenly I am getting errors with the following function: SELECT incr(max_price($1),0.05) 000810.17:20:41.181 [2246] ERROR: Bad float8 input format '0.05' 000810.17:20:41.181 [2246] AbortCurrentTransaction Where incr() is defined as: CREATE FUNCTION "incr" (float8,float8 ) RETURNS float8 AS ' SELECT CASE WHEN $1 dpow(10,int8(log($1))+1)/2 THEN (dpow(10,int8(log($1 * $2 ELSE (dpow(10,int8(log($1))+1)/2) * $2 END ' LANGUAGE 'SQL'; Strangely engough the function call works fine when called from psql but fails (but not always!) from a C trigger. Thanks in advance for any help, -- Louis-David Mitterrand - [EMAIL PROTECTED] - http://www.apartia.org "Kill a man, and you are an assassin. Kill millions of men, and you are a conqueror. Kill everyone, and you are a god." -- Jean Rostand
[GENERAL] BLOB DBI func() interface under postgres
Hello, In DBD::Pg one can read (line 134): $lobj_fd = $dbh-func($lobjId, $mode, 'lo_open'); But how is the LOB retrieved in the first place? If I pass the OID of an existing LOB instance from a table the returned $lobj_fd is null. What kind of $lobjId is one supposed to pass to this function to open a LOB? The aim is to be able to read a LOB from a postgres DB without having to lo_export the object to a file first. Can that be done with the $dbh-func() interface? (using lo_open, lo_read, etc ..) TIA -- Louis-David Mitterrand - [EMAIL PROTECTED] - http://www.apartia.fr Hi, I am an alien .sig, and at the moment I am having sex to your mind, by looking at your smile I can see that you like it.
Re: [GENERAL] child table doesn't inherit PRIMARY KEY?
On Sun, Jun 04, 2000 at 03:46:53AM +0200, Peter Eisentraut wrote: Louis-David Mitterrand writes: When creating a child (through CREATE TABLE ... INHERIT (parent)) it seems the child gets all of the parent's contraints _except_ its PRIMARY KEY. Is this normal? It's kind of a bug. Is it a well-known bug or have I discovered it? ;-) (I am sending a copy of the bug report to -hackers) Thanks, -- Louis-David Mitterrand - [EMAIL PROTECTED] - http://www.apartia.fr "God is a mathematician of very high order, and he used very advanced mathematics in constructing the universe." (Dirac)
[GENERAL] child table doesn't inherit PRIMARY KEY?
When creating a child (through CREATE TABLE ... INHERIT (parent)) it seems the child gets all of the parent's contraints _except_ its PRIMARY KEY. Is this normal? Should I add a PRIMARY KEY(id) statement each time I create an inherited table? Cheers, -- Louis-David Mitterrand - [EMAIL PROTECTED] - http://www.apartia.fr If at first you don't succeed, redefine success.
[GENERAL] Re: child table doesn't inherit PRIMARY KEY?
On Sat, Jun 03, 2000 at 05:22:56PM +0200, Louis-David Mitterrand wrote:
When creating a child (through CREATE TABLE ... INHERIT (parent)) it
seems the child gets all of the parent's contraints _except_ its PRIMARY
KEY. Is this normal? Should I add a PRIMARY KEY(id) statement each time
I create an inherited table?
Following up to my previous message, I found that one can't explicitely
add a PRIMARY KEY on child table referencing a field on the parent
table, for instance:
CREATE TABLE auction (
id SERIAL PRIMARY KEY,
title text,
... etc...
);
then
CREATE TABLE auction_dvd (
zone int4,
PRIMARY KEY("id")
) inherits("auction");
doesn't work:
ERROR: CREATE TABLE: column 'id' named in key does not exist
But the aution_dvd table doesn't inherit the auction table's PRIMARY
KEY, so I can insert duplicates.
Solutions:
1) don't use PRIMARY KEY, use UNIQUE NOT NULL (which will be inherited?)
but the I lose the index,
2) use the OID field, but it's deprecated by PG developers?
What would be the best solution?
TIA
--
Louis-David Mitterrand - [EMAIL PROTECTED] - http://www.apartia.fr
Veni, Vidi, VISA.
[GENERAL] plperl extensions
How mature is the plperl language for writing functions and triggers? Is it fast? (ie: does the interpreter pre-compiles functions and persists?) I couldn't find any plperl package in the PG-7.0beta5 (the latest) debian packages, should I roll my own PG to get it? Thanks in advance for your help, PS: I noticed the mbox downloads are broken in the mailing-list archives on postgresql.org and there is no search interface. I'd be willing to host one but first I need to be able to download the mboxes ;-) -- Louis-David Mitterrand - [EMAIL PROTECTED] - http://www.apartia.fr
[GENERAL] fmgr_info error
After creating a trigger on an insert I get this error:
auction= insert into bid values('mito',3,354);
NOTICE: you bid the exact increment of 5
ERROR: fmgr_info: function 38667: cache lookup failed
And the insert is not performed as it should. What does this error mean?
TIA
--
Louis-David Mitterrand - [EMAIL PROTECTED] - http://www.apartia.fr
[GENERAL] crash on \copy
Is this a known problem? template1= \copy psql: xstrdup: cannot duplicate null pointer (internal error) styx:~% using PG 7.0-beta5 on Linux -- Louis-David Mitterrand - [EMAIL PROTECTED] - http://www.apartia.fr
[GENERAL] rules on INSERT can't UPDATE new instance?
From the create_rule man page this example is offered: CREATE RULE example_5 AS ON INERT TO emp WHERE new.salary 5000 DO UPDATE NEWSET SET salary = 5000; But what is "NEWSET"? Is it a keyword? My problem is that on an insert with an invalid amount I try to perform an update with a corrected amount, but the action part of the rule doesn't affect or "see" the newly inserted row (or so it seems). I tried: CREATE RULE ON INSERT TO bid WHERE new.price limit DO UPDATE bid SET price = 0.1; and all price columns in the bid table would be set to 0.1 _except_ the newly inserted row. Am I missing something obvious? TIA -- Louis-David Mitterrand - [EMAIL PROTECTED] - http://www.apartia.fr
