Re: [GENERAL] Equivalence Classes when using IN
On Mon, Oct 09, 2017 at 07:44:50PM -0400, Tom Lane wrote: > David Rowley writes: > > If the only reason that is_simple_subquery() rejects subqueries with > > ORDER BY is due to wanting to keep the order by of a view, then > > couldn't we make is_simple_subquery() a bit smarter and have it check > > if the subquery is going to be joined to something else, which likely > > would destroy the order, or at least it would remove any guarantees of > > it. > > I'm not on board with this. The assumption is that if the user put an > ORDER BY there, that means they want that subquery to be computed in that > order. It's not for us to decide they didn't mean what they said. > > Moreover, there are cases where the ORDER BY would be semantically > significant, eg if there's a LIMIT or volatile functions or tSRFs > involved. Or where the order is meaningful to an aggregate function applied to columns of a view result set. I'm not sure what the full set of cases where the ORDER BY on the inner query is meaningful, but I'm sure there are cases it is not. If there are no such constraints on dropping the ORDER BY, then the it could be dropped, making the view query simpler. Nico -- -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Postgresql CDC tool recommendations ?
On Thu, Oct 05, 2017 at 10:28:31AM -0700, avi Singh wrote: > Any recommendation on a good CDC tool that can be used to push > postgresql changes to Kafka in json format ? There are quite a few WAL->JSON type tools out there. E.g., https://github.com/eulerto/wal2json I expect this to improve with PG 10 logical replication. You can easily add the bit that pushes those JSON texts to Kafka. Nico -- -- 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] time series data
You have these choices: - turn events into INSERTs and UPDATES on a table that represents a single call You might have an events VIEW with INSTED OF insert/update triggers so you can insert events as the interface for updating calls. - store the events and have a VIEW on the events table that gives you rows that summarize each call - both: store the events and the summaries of the calls You might have an events table with AFTER INSERT triggers to insert or update the corresponding rows in the calls table. Nico -- -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] COMMIT TRIGGER implementation using CONSTRAINT TRIGGERs
I've written an approximation of "commit triggers" for PostgreSQL using CONSTRAINT TRIGGERs (as users have often been told to do who want this feature). Semantics (and a warning) are included in commentary in the source: https://github.com/twosigma/postgresql-contrib/ https://github.com/twosigma/postgresql-contrib/blob/master/commit_trigger.sql https://raw.githubusercontent.com/twosigma/postgresql-contrib/master/commit_trigger.sql I suspect a number of users here may find this useful. I've also posted separately to pgsql-hackers to see if we can get agreement that commit triggers are a reasonable and desirable feature. Cheers, Nico -- -- 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] Aquameta 0.1 - Request for reviews, contributors
Here's a review comment. Just one for now. Looking at the meta module, I see things like this: execute 'select (count(*) = 1) from ' || quote_ident((row_id::meta.schema_id).name) || '.' || quote_ident((row_id::meta.relation_id).name) || ' where ' || quote_ident((row_id.pk_column_id).name) || ' = ' || quote_literal(row_id.pk_value) into answer; I recently learned what I find to be a better idiom: execute format( $q$ select exists (select * from %1$I.%2$I where %3$I = %4$L); $q$, -- interpolated arguments here (row_id::meta.schema_id).name, (row_id::meta.relation_id).name, (row_id.pk_column_id).name, row_id.pk_value into answer; That is, PostgreSQL has extended string literal syntax where you can use $stuff$ instead of single-quotes, and that makes it much easier to write dynamic (generated for EXECUTE) SQL. In particular, because your $EDITOR [generally] won't recognize this, syntax highlighting for the $quoted$ code will work as expected! This is better not only because it's more concise, easier to line-wrap, and easier on the eyes, but also because you get to use format(). I suspect using format() makes it harder to forget to quote something appropriately -- harder to accidentally create a SQL injection vulnerability. I usually use argument numbering (%$I) instead of referring to the positionally (%I, %L, %s) because it helps a lot whenever I need to refer to one of them multiple times. Of course, this is just a matter of style, but I strongly feel that this is the superior style (at least I find or stumble into a better style), especially when you have several layers of trigger functions creating more trigger functions, as you can easily nest $foo$-quoted string literals by having different quote forms for each level. Also, I used exists() instead of count(*) = 1 -- that's just my personal preference, and a less defensible style matter (it is more verbose...). Nico -- -- 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] Schema/table replication
On Wed, Sep 06, 2017 at 08:22:14AM -0700, Steve Atkins wrote: > > On Sep 6, 2017, at 6:00 AM, Marcin Giedz wrote: > > > > Hi, is there any way (3rd party software) to replicate particular > > schema/table not the whole database with streaming replication built-in > > mechanism ? > > I don't believe so. You can do that with logical replication in v10 - > https://www.postgresql.org/docs/10/static/logical-replication.html. Well, session_replication_role exists so that you can implement your own logical replication, and with that you can have full control over what to replicate and what not replicate. There is no builtin functionality that does this. Third-party software? Try this search and similar in other engines: https://github.com/search?utf8=%E2%9C%93&q=session_replication_role+extension%3Asql&type=Code&ref=advsearch&l=&l= > pglogical will give you much the same functionality on current > releases. https://www.2ndquadrant.com/en/resources/pglogical/ - > installation isn't too painful (though the docs are a little sparse > when it comes to which node you should run which command on. Make the > postgres.conf changes on master and slave nodes, as slave nodes need > replication slots too(?)). > > There are a bunch of trigger-based replication frameworks that'll work > too, though less efficiently - Slony is widely used, and I used > Bucardo successfully for years before moving to pglogical. Yeah. -- 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] Create Action for psql when NOTIFY Recieved
On Tue, Sep 05, 2017 at 08:19:13AM -0700, Steve Atkins wrote: > > On Sep 4, 2017, at 10:25 PM, Nico Williams wrote: > > On Mon, Sep 4, 2017 at 4:21 PM Steve Atkins wrote: > > > https://github.com/wttw/pgsidekick [BTW, I must say I like pgsidekick, but for the use of the payload bit.] > > But the question i have is: how to get such functionality integrated > > into PostgreSQL? Is a standalone program (plus manpage plus > > Makefile changes) enough, or would a psql \wait command be better? > > There's not really any need to integrate it into postgresql at all. It > doesn't rely on any details of the core implementation - it's just a > normal SQL client, a pretty trivial one. It's a bit of an FAQ though, isn't it. I do think it odd that PG has this functionality on the server side and in the client-side API, but its client-side utility functionality for it is very limited. > (Whether psql could usefully be reworked to listen for activity on the > connection when it's not actively executing a query is another > question). A \wait would simply wait for notifications from the server. It would be interruptible by ^C, but it would not listen for input on stdin. I think that should be a simple-enough patch to psql. Nico -- -- 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] Create Action for psql when NOTIFY Recieved
On Mon, Sep 4, 2017 at 4:21 PM Steve Atkins wrote: > > > > Me too. > > https://github.com/wttw/pgsidekick > > Select-based, sends periodic keep-alives to keep the connection open, > outputs payloads in a way that's friendly to pipe into xargs. (Also the > bare bones of a notify-based scheduler). Without any kind of access controls on NOTIFY channels, nor any kind of payload validation, i just don't feel comfortable using the payload at all. Besides, the payload is hardly necessary given that there's a database on which you can scribble the payload :) It suffices that you receive a notification, and you can then check if there's anything to do. My version of this doesn't have connection keepalives, but that's ok because that can be added in the form of notifications, and the consumer of pqasyncnotifier can implement timeouts. But i agree that timeouts and keepalives would be nice, and even invoking a given SQL function would be nice. But the question i have is: how to get such functionality integrated into PostgreSQL? Is a standalone program (plus manpage plus Makefile changes) enough, or would a psql \wait command be better? Nico -- > >
Re: [GENERAL] Create Action for psql when NOTIFY Recieved
On Sun, Sep 03, 2017 at 05:37:57PM -0500, Nico Williams wrote: > What would it take to have pqasyncnotifier [0] adopted by PostgreSQL? Maybe it should be named pqasynclisterner. A \wait command for psql could do the same thing. I could probably write such a patch at some point if there's interest, something like: \wait [[N] [statement]] that waits for N NOTIFYies (or forever if N is -1), perhaps always printing the payload, but with newlines escaped (or truncated at newlines) to avoid needing options, and runs a statement if provided. Nico -- -- 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] Create Action for psql when NOTIFY Recieved
[I meant to send this to the list] On Mon, Aug 28, 2017 at 07:08:28PM -0400, Tom Lane wrote: > "David G. Johnston" writes: > > On Mon, Aug 28, 2017 at 1:28 PM, Jerry Regan < > > jerry.re...@concertoglobalresources.com> wrote: > >> My concern is how, after LISTENing in psql, I can tell it what to do when > >> the NOTItFY is received. > > > As far as I am aware you cannot. > > Yes, and psql is not designed to do anything of its own accord, > so I think the answer is really "use another program". > > > "Whenever a command is executed, psql also polls for asynchronous > > notification events generated by LISTEN and NOTIFY." > > Exactly. If you don't feed it a command, it just sits there. > > > I suspect the feature request would be something like: > > \set NOTIFY_PROGRAM './process-notify-request.bash' (or an equivalent > > meta-command) > > And psql would invoke said program and pass the content of the notification > > payload to it via stdin. > > Such a program could only execute after the next time you give a command > to psql. You could maybe imagine feeding it a continuous stream of dummy > commands, but that's pretty silly (and rather defeats the point of LISTEN, > which is to *not* eat cycles while waiting). What would it take to have pqasyncnotifier [0] adopted by PostgreSQL? pqasyncnotifier solves all the problems that psql has regarding LISTENing for notifications. Note too that pqasyncnotifier doesn't poll, rather, it blocks in PQconsumeInput(). [0] https://github.com/twosigma/postgresql-contrib/blob/master/pqasyncnotifier.c (Oy, I just noticed that the PQfinnish() call needs to move up to the end of the for (;;) loop...) Nico -- -- 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] Create Action for psql when NOTIFY Recieved
My principal problem with psql(1) relative to NOTIFY/LISTEN is that
psql(1) won't check for them until it has had some input on stdin. So
it will appear to do nothing when it's idle, even if there millions of
notifies for it to respond to!
So I wrote a program to just LISTEN:
https://github.com/twosigma/postgresql-contrib/blob/master/pqasyncnotifier.c
With that you will get a line of output per-notification (unless you
request printing the payload and the payload has embedded newlines, so
watch out!). You can then use this to drive actions in a script. For
example:
#!/bin/bash
if (($# != 1)); then
printf 'Usage: %s POSTGRESQL-URI\n' "${0##*/}"
exit 1
fi
pqasyncnotifier "$1" notify_channel1 channel2 | while read junk; do
printf 'SELECT do_thing();\n'
done | psql -f - "$1"
You can listen on one or more channels, print the channel name, PID,
timestamp (local to the pqasyncnotifier), and/or NOTIFICATION payload:
#!/bin/bash
if (($# < 2)); then
printf 'Usage: %s POSTGRESQL-URI CHANNEL [CHANNEL ...]\n' "${0##*/}"
exit 1
fi
pqasyncnotifier -c "$@" | while read junk channel; do
printf 'SELECT do_thing(%s);\n' "$channel"
done | psql -f - "$1"
Be _very_ careful about using the NOTIFY payload (option -d) though:
it's completely unconstrained in form and contents, and anyone can
NOTIFY on any channel as there are no access controls on channels (you
don't even have to create them, and there's no CREATE for them anyways).
The right thing to do is to not bother with the payload at all -- just
the mere fact that a NOTIFY was done on some channel should be all
that's required for any processes LISTENing on that channel.
I might modify pqasyncnotifier to either truncate payloads at newlines,
or escape/remove newlines so that it could be safer to use the payloads.
I would like to see PostgreSQL adopt this program!
Nico
--
--
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] Strange SQL result - any ideas.
On Fri, Sep 01, 2017 at 11:08:32PM +0100, Paul Linehan wrote:
> which is fine (note that the field "mary" is sorted correctly) but
> I want "proper" JSON - i.e. with open and close square brackets
> i.e. ([ - ]) before and after the fields!
I don't know what that means. Do you mean that you want all the rows in
one large top-level array?
First, JSON no longer requires that texts be either objects or array at
the top level. But it is true that only one value may be present at the
top level, though many DBs produce sequences of multiple texts separated
by newlines.
Anyways, the thing to do is to use json_agg() or jsonb_agg(), like so:
SELECT json_agg(row_to_json(t)) FROM (SELECT *
FROM fred
ORDER BY mary, jimmy, paulie) t;
> So, I tried this query:
That's pretty hacky. Of course, it's also online/streaming, which
aggregates are not.
> SELECT '[' AS my_data -- <<-- added line
> UNION -- <<-- added line
> SELECT REGEXP_REPLACE(ROW_TO_JSON(t)::TEXT, '', '\\', 'g')
> FROM
> (
> SELECT * FROM fred
> ORDER BY mary, jimmy, paulie
> ) AS t
> UNION -- <<-- added line
> SELECT ']';-- <<-- added line
>
> *_BUT_*, this gives
>
>
>my_data
> --
> ]
> [
> {"mary":6,"jimmy":43343,"paulie":"eresdfssfsfasfae"}
> {"mary":35,"jimmy":5,"paulie":"wrew\sdfsd"}
> {"mary":3,"jimmy":44545,"paulie":"\sdfs\\\sfs\\gf"}
> {"mary":2,"jimmy":43,"paulie":"asfasfasfd"}
> {"mary":3,"jimmy":435,"paulie":"ererere"}
> (7 rows)
The order of rows is undefined given that you don't have an ORDER BY in
the outer-most query. If you used UNION ALL it might work the way you
want, but then again, it might not.
This might work better if you must have the online property:
SELECT q.token FROM (
SELECT '[' AS token, 0 AS n, NULL AS mary, NULL AS jimmy, NULL AS paulie
UNION -- ALL or not ALL works equally well, but if fred has no dups
-- then UNION ALL will be faster
SELECT regexp_replace(row_to_json(fred)::TEXT, '', '\\', 'g'),
1, fred.mary, fred.jimmy, fred.paulie
FROM fred fred
UNION -- ALL or not ALL works equally well, but if fred has no dups
-- then UNION ALL will be faster
SELECT ']', 2, NULL, NULL, NULL) q
ORDERY BY q.n, q.mary, q.jimmy, q.paulie;
> Two problems with this result - one is that my square brackets are not in
> the right place - this at least I understand - the first character of
> each line is sorted by its ASCII value - '[' comes before ']' (naturally)
> and '{' comes after them both - or have I got that right?
>
> But, I do *_not_* understand why my table data is now out
> of sort order - I've looked at it and can't see *_how_* the sort
> order in my table data has been determined.
UNION means "filter out duplicates", which may be implemented via a hash
table that doesn't preserve insertion order.
Nico
--
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Can not change log_min_duration_statement parameter on PG 8.2.4
Hello,
I am running a 8.2.4 PostgreSQL instance on a debian etch server.
I have a problem trying to change the parameter log_min_duration_statement.
Its actuel value in the postgresql.conf is "-1" (log off) :
log_min_duration_statement = -1# -1 is disabled, 0 logs all
statements
# and their durations.
I reloaded (even restarted) the service, but when I connect to any
database (even a newly created one), the log_min_duration_statement is
still "0".
And effectively, all the queries executed on the server are logged.
The parameter does not seem to be set for the database though :
postgres=# SELECT datconfig from pg_database where datname = 'postgres' ;
datconfig
---
(1 ligne)
If I set a new value for the database with an ALTER DATABASE command,
then disconnect and reconnect, the value is still "0" :
postgres=# ALTER DATABASE postgres SET log_min_duration_statement to 2000;
ALTER DATABASE
postgres=# \q
12:29| r...@myserver:~ # psql -U postgres
Bienvenue dans psql 8.2.4, l'interface interactive de PostgreSQL.
Tapez: \copyright pour les termes de distribution
\h pour l'aide-mémoire des commandes SQL
\? pour l'aide-mémoire des commandes psql
\g ou point-virgule en fin d'instruction pour exécuter la requête
\q pour quitter
postgres=# SELECT datconfig from pg_database where datname = 'postgres' ;
datconfig
---
{log_min_duration_statement=2000}
(1 ligne)
postgres=# SHOW log_min_duration_statement ;
log_min_duration_statement
0
(1 ligne)
postgres=#
Though I can set a new value for one session :
postgres=# SET log_min_duration_statement to 2500;
SET
postgres=# SHOW log_min_duration_statement ;
log_min_duration_statement
2500ms
(1 ligne)
Am I missing something ?
Thanks for your help !
Nico
--
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] Delphi connection ?
Thank you to all for the interesting replies ! Best regards, N. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Delphi connection ?
Hi List, I tried already in the ODBC list, but without success... I have to develop a client/server application, heavily transaction oriented, that will serve around 100 users and database traffic will be intense (lot's of reads, updates, inserts). Development environment is Delphi 2007. I know there are a few commercial components available, but I was wondering if ODBC could do the job ? So, my question is, if ODBC is intended to be used for that ? Many simultanous connections, lots of inserts, updates ? The thing you always hear about ODBC is, that it is very slow ? Many thanks in advance, Best regards, Nico Callewaert
Re: [GENERAL] I can't drop a user if I don't drop his grants beforehand??????????????????
Alvaro Herrera ha scritto: Nico Sabbi wrote: Hi, i can't believe my eyes. Why on earth I can't drop a user without previously revoking his privileges? This is really _crazy_ in my opinion. I'm not speaking of object ownership, but of GRANTs. As Tom says, it's a known limitation. Did you try REASSIGN OWNED and/or DROP OWNED? Hi, sorry for the big delay. No, I didn't because the tables weren't owned by the user I wanted to drop, but by another 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] I can't drop a user if I don't drop his grants beforehand??????????????????
Tom Lane ha scritto: Nico Sabbi writes: i can't believe my eyes. Why on earth I can't drop a user without previously revoking his privileges? Yeah, it's a known limitation. The reason it's not implemented is that some of the privileges may be in other databases besides the particular one you're connected to, and the backend hasn't got any way to get at those databases to remove the entries. Maybe someday it'll get fixed, but don't hold your breath. regards, tom lane Thanks. Unfortunately role_usage_grants is emtpy and usage_privileges doesn't contain what I expected to find, so I had to iterate over the distinct table_schema in iformation_schema.tables to drop the schema_usage grants. Nico -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] I can't drop a user if I don't drop his grants beforehand??????????????????
Hi, i can't believe my eyes. Why on earth I can't drop a user without previously revoking his privileges? This is really _crazy_ in my opinion. I'm not speaking of object ownership, but of GRANTs. Why? -- 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] After Upgrade from 8.2.6 to 8.3.6: function to_timestamp does not exist
If you're going to truncate the NOW(), just go with CURRENT_DATE instead. Thanks for the "CURRENT_DATE" tip, Adam. Works fine! -- 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] After Upgrade from 8.2.6 to 8.3.6: function to_timestamp does not exist
OK, so you want to see if a timestamp is greater than now()? Why not
just compare them?
where a.from_datetime >= now()
No, not the whole timestamp. I dont want to check the time.
So I had to truncate the datetime with:
date_trunc('day', a.from_datetime) >= date_trunc('day', NOW())
--
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] After Upgrade from 8.2.6 to 8.3.6: function to_timestamp does not exist
This query makes little sense. Why are you trying to convert a
timestamp to a timestamp? Is this a bizarre substitute for date_trunc()?
Got it:
Thanks for the "date_trunc" tip.
This query works fine:
date_trunc('day', a.from_datetime) >= date_trunc('day', NOW())
--
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] After Upgrade from 8.2.6 to 8.3.6: function to_timestamp does not exist
This query makes little sense. Why are you trying to convert a timestamp to a timestamp? Is this a bizarre substitute for date_trunc()? The "from_datetime" column is of type "timestamp" but I want to check only the date, not the time. In this example I want to retrieve all records whose "from_datetime" is e.g. >= 2009/05/06 (Now()) so I'd like to get results with a "from_datetime" like e.g. - 2009/05/06 00:05:00 - 2009/05/06 23:30:00 - 2009/05/07 10:15:00 Regards Nico -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] After Upgrade from 8.2.6 to 8.3.6: function to_timestamp does not exist
Dear list members I have upgraded my PostgreSQL 8.2.6 to 8.3.6 and I am having trouble with calling "to_timestamp" function. Here is the query I use: SELECT a.* FROM tblevent a WHERE to_timestamp(a.from_datetime,'/MM/DD') >= to_timestamp( NOW(),'/MM/DD' ) ORDER BY a.from_datetime In PostgreSQL 8.2.6 everything works fine. In PostgreSQL 8.3.6 I get the following error: ERROR: function to_timestamp(timestamp without time zone, unknown) does not exist LINE 3: WHERE to_timestamp(a.from_datetime,'/MM/DD') >= to_tim... ^ HINT: No function matches the given name and argument types. You might need to add explicit type casts. Is the "to_timestamp" function not supported anymore in 8.3.6? I could not read anything about it in the 8.3 documentation. Regards Nico -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Function parameter
- Original Message - From: "John DeSoi" To: "Nico Callewaert" Cc: Sent: Wednesday, February 25, 2009 1:52 AM Subject: Re: [GENERAL] Function parameter On Feb 24, 2009, at 5:10 PM, Nico Callewaert wrote: I'm trying to modify an input parameter of a function, but I receive following error : ERROR: "$17" is declared CONSTANT CONTEXT: compile of PL/pgSQL function "update_jobreg" near line 26 Is there a way to modify an input parameter or I have to declare a local variable and assign that input parameter to it ? Declaring a local variable is the best way to do it. You can modify a parameter if you declare it as INOUT, but you generally only want to do that if you want to return something from the function. Note that you can declare and assign the value in a single line in the DECLARE section of the function, e.g. text_var text := text_param; Hi ! Thank you for the explanation. I was not warae of the fact that you could declare and assign a variable in 1 line. Thanks, best regards, Nico -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Function parameter
Hi ! I'm trying to modify an input parameter of a function, but I receive following error : ERROR: "$17" is declared CONSTANT CONTEXT: compile of PL/pgSQL function "update_jobreg" near line 26 Is there a way to modify an input parameter or I have to declare a local variable and assign that input parameter to it ? Many thanks in advance, Nico Callewaert
Re: [GENERAL] Elapsed time between timestamp variables in Function
Thanks a lot to everybody for the help ! - Original Message - From: "Osvaldo Kussama" To: "Nico Callewaert" Cc: Sent: Thursday, February 05, 2009 2:59 AM Subject: Re: [GENERAL] Elapsed time between timestamp variables in Function 2009/2/4 Nico Callewaert : Hi ! I saw previous postings about elapsed time between 2 timestamps, using SELECT EXTRACT... I have similar question, but it's not in a select statement, but between 2 variables in a function. To keep it simple, I have 2 variables, let's say A and B, both TimeStamp. Now I would like to know the absolute value of elapsed seconds between the 2 timestamps. Has to be absolute value, because can be positive or negative, depends if A > B or A < B. I tried with age(A, B), but that gives me something like 00:00:01, not really numeric value for number of seconds. Many thanks in advance ! Nico EXTRACT(EPOCH FROM age(A,B)) ? Osvaldo -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Elapsed time between timestamp variables in Function
Hi ! I saw previous postings about elapsed time between 2 timestamps, using SELECT EXTRACT... I have similar question, but it's not in a select statement, but between 2 variables in a function. To keep it simple, I have 2 variables, let's say A and B, both TimeStamp. Now I would like to know the absolute value of elapsed seconds between the 2 timestamps. Has to be absolute value, because can be positive or negative, depends if A > B or A < B. I tried with age(A, B), but that gives me something like 00:00:01, not really numeric value for number of seconds. Many thanks in advance ! Nico
Re: [GENERAL] case dumbiness in return from functions
Tom Lane ha scritto: Ivan Sergio Borgonovo <[EMAIL PROTECTED]> writes: After discovering that pg_get_serial_sequence behaves in a bit strange way[1] when it deals to case sensitiveness The SQL standard specifies that unquoted identifiers are case-insensitive. You're welcome to spell them as camelCase in your source code if you feel like it, but don't expect that PG, or any other SQL-compliant database, will pay attention. regards, tom lane yet I find disturbing that Postgres doesn't make the effort to respect the case specified by the user. If I created a field called "REF" why should Postgres call it "ref" in the output of queries if the standard doesn't specify any obligation to convert the name ? I'd like to have the possibility to enable this feature in future releases. As for portability: it may not be a concern when you have at disposal the best DB around :) ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] Inconsistence in transaction isolation docs
Albe Laurenz ha scritto:
Nico Sabbi wrote:
/From:
http://www.postgresql.org/docs/8.2/interactive/transaction-iso.html
"
Read Committed/ [...]
to me the above sentence sounds inconsistent: it's
asserting that both 1) and 2) apply:
1) it never sees ... changes committed during query
execution by concurrent transactions
2) Notice that two successive SELECT commands can see
different data, even though they are within a single
transaction, if other transactions commit changes
during execution of the first SELECT
Can anyone explain, please?
1) means: as long as the first SELECT runs ("during
query execution"), you won't see changes made by
another transaction.
2) means: when you run a second SELECT, that SELECT
will see changes made by other transactions, even if
both SELECTs are in one (read commited) transaction.
That doesn't sound contradictory to me.
There is a difference between "during query execution"
and "within a single transaction", maybe that is where
your problem comes from.
Yours,
Laurenz Albe
well, I know how read committed behaves, but
I don't see why should anyone expect an update of the resultset
of the currently executing query after a commit by a different
transaction.
Thanks everybody who replied,
Nico
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
[GENERAL] Inconsistence in transaction isolation docs
/From: http://www.postgresql.org/docs/8.2/interactive/transaction-iso.html " Read Committed/ is the default isolation level in PostgreSQL. When a transaction runs on this isolation level, a SELECT query sees only data committed before the query began; it never sees either uncommitted data or changes committed during query execution by concurrent transactions. (However, the SELECT does see the effects of previous updates executed within its own transaction, even though they are not yet committed.) In effect, a SELECT query sees a snapshot of the database as of the instant that that query begins to run. Notice that two successive SELECT commands can see different data, even though they are within a single transaction, if other transactions commit changes during execution of the first SELECT. " to me the above sentence sounds inconsistent: it's asserting that both 1) and 2) apply: 1) it never sees ... changes committed during query execution by concurrent transactions 2) Notice that two successive SELECT commands can see different data, even though they are within a single transaction, if other transactions commit changes during execution of the first SELECT Can anyone explain, please? ---(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 - problem with NULL values
Stefan Schwarzer ha scritto: From 8.3 beta release notes: - ORDER BY ... NULLS FIRST/LAST I think this is what you want right? Yes, indeed. Sounds great. unfortunately I am on 8.1. And wouldn't really want to migrate to 8.3 and beta for the moment order by 1 ? ---(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] row->ARRAY or row->table casting?
Gregory Stark ha scritto: "Nico Sabbi" <[EMAIL PROTECTED]> writes: nb1=# select r.a from (select row(tab1.*)::tab1 as r from tab1)x; ERROR: missing FROM-clause entry for table "r" LINE 1: select r.a from (select row(tab1.*)::tab1 as r from tab1)x; ^ I tried many variations (including casting x as tab1) obtaining only syntax errors. r.a would be the column "a" in the table named "r", but the only table in the FROM list is "x". So you have to use a workaround to make it clear to the parser that you're referring to the column "r", it would look like SELECT (r).a from (select row(tab1.*)::tab1 as r from tab1)x; yes, it works. Thanks a lot, Nico ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] row->ARRAY or row->table casting?
Tom Lane ha scritto: Nico Sabbi <[EMAIL PROTECTED]> writes: is there any way to cast a generic row to an array or to a table type? "row(...)::composite_type" should work in 8.2 and up. regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq I'm using 8.2.4. What's the right syntax for a case like this? nb1=# select r.a from (select row(tab1.*)::tab1 as r from tab1)x; ERROR: missing FROM-clause entry for table "r" LINE 1: select r.a from (select row(tab1.*)::tab1 as r from tab1)x; ^ I tried many variations (including casting x as tab1) obtaining only syntax errors. Thanks, Nico ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[GENERAL] row->ARRAY or row->table casting?
Hi, is there any way to cast a generic row to an array or to a table type? The example is trivial, but it explains what I'm trying to do: nb1=# select * from tab1; a | t ---+--- 1 | a 2 | b 3 | c (3 rows) nb1=# select r from (select row(tab1.*) as r from tab1)x; r --- (1,a) (2,b) (3,c) (3 rows) nb1=# select r[1] from (select row(tab1.*) as r from tab1)x; ERROR: cannot subscript type record because it is not an array nb1=# select (r::tab1).a from (select row(tab1.*) as r from tab1)x; ERROR: cannot cast type record to tab1 The real use case is much more complex than this example of course :) ---(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
[GENERAL] Can't SELECT from (INSERT ... RETURNING)
I thought I could use the output of INSERT...RETURNING as a set of tuples for a subquery, but it seems it's not the case: nb1=# select * from (insert into m(a) values(112) returning a); ERROR: syntax error at or near "into" LINE 1: select * from (insert into m(a) values(112) returni... ^ Is this a bug or it's not even supposed to work in theory? Such a feature would be extremely useful to have. P.S. I know it's non-portable, but this is not a problem. Thanks, Nico ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
[GENERAL] REQUEST: option to auto-generate new sequences with CREATE TABLE (LIKE)
Hi, as the subjects reads I searched in the docs a way to instruct postgres to create new sequences when copying tables containing serial columns, but the resulting serial fields in the new tables reference the original sequence. Yes, there are workarounds, but having an option to make postgres automatically generate a new sequence for every serial field would be splendid. I hope you will consider this feature for one of the future versions of Postgres. Thanks, Nico ---(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] Some problem with warm standby server
Simon Riggs wrote: then I updated the master with a batch of inserts, but after a while the slave stopped with these messages: LOG: restored log file "00010021" from archive LOG: record with zero length at 0/2148 LOG: invalid primary checkpoint record LOG: restored log file "00010020" from archive LOG: restored log file "00010021" from archive LOG: invalid resource manager ID in secondary checkpoint record PANIC: could not locate a valid checkpoint record LOG: startup process (PID 19619) was terminated by signal 6 LOG: aborting startup due to startup process failure Please run pg_controldata to print out the control file. Hi, sorry for the long delay. First of all I had to stop postgres with pg_ctl stop -s immediate, or it wouldn't die because of the ongoing replication. This is the output of pg_controldata: [EMAIL PROTECTED]:/usr/local/postgres_replica/data$ pg_controldata /usr/local/postgres_replica/data/ pg_control version number:812 Catalog version number: 200510211 Database system identifier: 5001030714849737714 Database cluster state: in recovery pg_control last modified: Fri 27 Apr 2007 13:20:46 CEST Current log file ID: 0 Next log file segment:26 Latest checkpoint location: 0/190C7E04 Prior checkpoint location:0/190C7DC0 Latest checkpoint's REDO location:0/190C7E04 Latest checkpoint's UNDO location:0/0 Latest checkpoint's TimeLineID: 1 Latest checkpoint's NextXID: 3698809 Latest checkpoint's NextOID: 68745 Latest checkpoint's NextMultiXactId: 1 Latest checkpoint's NextMultiOffset: 0 Time of latest checkpoint:Fri 27 Apr 2007 11:53:47 CEST Maximum data alignment: 4 Database block size: 8192 Blocks per segment of large relation: 131072 Bytes per WAL segment:16777216 Maximum length of identifiers:64 Maximum columns in an index: 32 Date/time type storage: floating-point numbers Maximum length of locale name:128 LC_COLLATE: C LC_CTYPE: C Backup all the files in case we need to inspect them. ok What was the ending log sequence number (e.g. x/) from the previous recovery? I'll see if I can re-create this. judging from the logs I gues it is 0/190C7E04: LOG: restored log file "00010019.000C7E04.backup" from archive LOG: restored log file "00010019" from archive LOG: checkpoint record is at 0/190C7E04 LOG: redo record is at 0/190C7E04; undo record is at 0/0; shutdown FALSE LOG: next transaction ID: 3698809; next OID: 68745 LOG: next MultiXactId: 1; next MultiXactOffset: 0 LOG: automatic recovery in progress LOG: redo starts at 0/190C7E48 What did I do wrong? Is there any other procedure to follow to restart a stopped replication? You're right, using the trigger is not the right way to stop/start the standby. Just stop/start the standby server normally. as above: a plain stop hangs The trigger means that you'd like to perform a failover. There is a patch not yet applied which will make a new version of pg_standby. pg_standby's official status right now is beta, so please expect, look for and report any issues you find. Thanks. thank you ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[GENERAL] Some problem with warm standby server
Hi, I have some doubts regarding the settings and the access procedure of warm standby servers: - can autovacuum be safely enabled on the replicator? - I'm using pg_standby (from cvs) that is generally working well as expected (logs are copied with scp); today I wanted to temporarily stop the replication to verify some data to restart it later on, so I touched the trigger file, waited for the log to report "database ready", verified that the databases were actually up-to-date. All was fine, then I ran rm -f pg_xlog/* pg_xlog/archive_status/* mv recovery.done recovery.conf (the permissions were right) /etc/init.d/postgresql stop ; /etc/init.d/postgresql start the replication seemed to start: --- LOG: database system was shut down at 2007-04-27 12:16:13 CEST LOG: starting archive recovery LOG: restore_command = "/usr/local/bin/pg_standby -s 5 -w 0 -t /usr/local/postgres_replica/trigger /usr/local/postgres_replica/log/ %f %p" cp: cannot stat `/usr/local/postgres_replica/log//0001.history': No such file or directory cp: cannot stat `/usr/local/postgres_replica/log//0001.history': No such file or directory cp: cannot stat `/usr/local/postgres_replica/log//0001.history': No such file or directory then I updated the master with a batch of inserts, but after a while the slave stopped with these messages: LOG: restored log file "00010021" from archive LOG: record with zero length at 0/2148 LOG: invalid primary checkpoint record LOG: restored log file "00010020" from archive LOG: restored log file "00010021" from archive LOG: invalid resource manager ID in secondary checkpoint record PANIC: could not locate a valid checkpoint record LOG: startup process (PID 19619) was terminated by signal 6 LOG: aborting startup due to startup process failure What did I do wrong? Is there any other procedure to follow to restart a stopped replication? Thanks, Nico ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Help setting up warm standby replication
Nico Sabbi wrote: Merlin Moncure wrote: try link mode, not copy mode (-l). make sure you read about the -k switch. merlin replaced -c with -l, but the result was the same. I assume that with -k you mean the socket dir for postgres - since pg_standby doesn't seek to recognize that switch - but I didn't need it: I connected to -p 5433. Anyway, after having stopped the replication I had the same problem; in order to make the slave start I had to manually copy the log files from the archive to pg_xlog/ . Is pg_standby supposed to work with Postgres 8.2 ? or only with 8.1.? update: touching the right trigger makes the replication stop, thus I can connect to the server and see the updated data. Thanks a lot, Nico ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Help setting up warm standby replication
Merlin Moncure wrote: try link mode, not copy mode (-l). make sure you read about the -k switch. merlin replaced -c with -l, but the result was the same. I assume that with -k you mean the socket dir for postgres - since pg_standby doesn't seek to recognize that switch - but I didn't need it: I connected to -p 5433. Anyway, after having stopped the replication I had the same problem; in order to make the slave start I had to manually copy the log files from the archive to pg_xlog/ . Is pg_standby supposed to work with Postgres 8.2 ? or only with 8.1.? ---(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] Help setting up warm standby replication
Merlin Moncure wrote: On 4/16/07, Nico Sabbi <[EMAIL PROTECTED]> wrote: Is there any parameter that I have to pass to the second server to keep on requesting WALs? I still don't understand what instructs the server to continously request the master's logs. google pg_standby. I've set it up and it works. merlin Hi, I installed the second version of pg_standby.tar that you posted here. In recovery.conf I set restore_command = '/usr/local/bin/pg_standby -d -c -s 5 -w 0 /tmp/pg/ %f %p' and I see in the log file that something goes on after every update: Trigger file : (null) Waiting for WAL file : /tmp/pg//00010021 WAL file path: 00010021 Restoring to... : pg_xlog/RECOVERYXLOG Sleep interval : 5 seconds Max wait interval: 0 forever Command for restore : cp /tmp/pg//00010021 pg_xlog/RECOVERYXLOG running restore : success LOG: restored file "00010021" from archivio but "cp /tmp/pg//00010021 pg_xlog/RECOVERYXLOG" looks suspicious to me. Is it expected? should WAL files overwrite pg_xlog/RECOVERYXLOG ? If I stop the replication and start the slave after having removed the recovery.conf the server doesn't come up: LOG: aborting startup due to startup process failure LOG: database system was interrupted while in recovery at log time 2007-04-16 15:29:42 CEST HINT: If this has occurred more than once some data may be corrupted and you may need to choose an earlier recovery target. LOG: impossibile aprire il file "pg_xlog/00010009" (file registro 0, segmento 9): No such file or directory LOG: invalid primary checkpoint record LOG: impossibile aprire il file "pg_xlog/00010021" (file registro 0, segmento 33): No such file or directory LOG: invalid secondary checkpoint record PANIC: impossibile localizzare un checkpoint record valido LOG: startup process (PID 10824) was terminated by signal 6 LOG: aborting startup due to startup process failure infact pg_xlog/ of the slave contains only RECOVERHISTORY: cat pg_xlog/RECOVERYHISTORY START WAL LOCATION: 0/920 (file 00010009) STOP WAL LOCATION: 0/A00 (file 0001000A) CHECKPOINT LOCATION: 0/920 START TIME: 2007-04-16 15:29:42 CEST LABEL: label STOP TIME: 2007-04-16 15:31:39 CEST Did I do something wrong? Thanks for your help, Nico ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Help setting up warm standby replication
Tom Lane wrote: Nico Sabbi <[EMAIL PROTECTED]> writes: To begin with I followed the example of the docs: in the recovery.conf file of the slave instance I set restore_command = 'cp -av /var/lib/pgsql/data/pg_xlog/%f %p' Hm, it looks like you are trying to copy xlog segments straight from the pg_xlog directory of the master? That's not going to work, because the master will rename/overwrite those files as soon as it thinks it's done with them. You need to have an archive_command on the master that is really truly copying the data to somewhere else, and then the restore_command should copy from the somewhere else. after having set up shared directory (one for archive_command and one for recover_command) I could successfully start and sync the slave dbms, but after the initial recovery the file recovery.conf is renamed to recovery.done and no more updates/WAL are asked (the slave's log reads: LOG: archive recovery complete LOG: system database ready ) Is there any parameter that I have to pass to the second server to keep on requesting WALs? I still don't understand what instructs the server to continously request the master's logs. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[GENERAL] Help setting up warm standby replication
Hi, after having read the documentation and the few posts in this list I tried to set up a warm standby replication between two instances of postgres running on my pc in 2 different base directories and 2 different ports. (The second one was a cp -a of the first one after having created a backup point) . I don't need a realtime update: even few minutes of lag are good enough for my needs. To begin with I followed the example of the docs: in the recovery.conf file of the slave instance I set restore_command = 'cp -av /var/lib/pgsql/data/pg_xlog/%f %p' but at restart I got these messages in the logs (some translation from italian to english by me) : LOG: system shutdown at 2007-04-13 13:02:51 CEST LOG: starting archive recovery LOG: restore_command = "cp -av /var/lib/pgsql/data/pg_xlog/%f %p" cp: impossibile fare stat di `/var/lib/pgsql/data/pg_xlog/0001.history': No such file or directory `/var/lib/pgsql/data/pg_xlog/00010007' -> `pg_xlog/RECOVERYXLOG' LOG: restored file "00010007" from archive LOG: invalid record length at 0/7000218 LOG: invalid primary record checkpoint `/var/lib/pgsql/data/pg_xlog/00010007' -> `pg_xlog/RECOVERYXLOG' LOG: restored file "00010007" from archive LOG: invalid record length at 0/70001D0 LOG: invalid primary record checkpoint PANIC: impossible locating a valid record checkpoint LOG: startup process (PID 20250) was terminated by signal 6 LOG: aborting startup due to startup process failure same thing if I empty the pg_xlog/ dir (except archive_status/) I hope someone can explain what I did wrong. Another thing I didn't understand is the continous replication mechanism: will the slave server periodically call the recovery_command or do I have to setup a cronjob to instruct it to search updates? Sorry if these questions are stupid :) and thanks in advance. Nico ---(end of broadcast)--- TIP 6: explain analyze is your friend
[GENERAL] gmake Error "/libpython2.4.a: could not read symbols: Bad value" with ./configure --with-python
Dear list members, I am trying to install Postgresql-8.2.3 on a Suse Linux Enterprise Server 10.1 64-Bit with Python 2.4.4. At the "gmake" command I get 2 errors (see below). I ran: ./configure --with-python --with-openssl gmake Here are the last lines of the gmake process: --- gcc -O2 -Wall -Wmissing-prototypes -Wpointer-arith -Winline -Wdeclaration-after-statement -Wendif-labels -fno-strict-aliasing -fpic -shared -Wl,-soname,libplpython.so.0 plpython.o -L/usr/local/lib/python2.4/config -L../../../src/port -lpython2.4 -lpthread -ldl -lutil -lm -Wl,-rpath,'/usr/local/lib/python2.4/config' -o libplpython.so.0.0 /usr/lib64/gcc/x86_64-suse-linux/4.1.0/../../../../x86_64-suse-linux/bin/ld: /usr/local/lib/python2.4/config/libpython2.4.a(abstract.o): relocation R_X86_64_32 against `a local symbol' can not be used when making a shared object; recompile with -fPIC /usr/local/lib/python2.4/config/libpython2.4.a: could not read symbols: Bad value collect2: ld returned 1 exit status gmake[3]: *** [libplpython.so.0.0] Error 1 gmake[3]: Leaving directory `/usr/local/src/postgresql-8.2.0/src/pl/plpython' gmake[2]: *** [all] Error 1 gmake[2]: Leaving directory `/usr/local/src/postgresql-8.2.0/src/pl' gmake[1]: *** [all] Error 2 gmake[1]: Leaving directory `/usr/local/src/postgresql-8.2.0/src' gmake: *** [all] Error 2 --- My system: + Suse Linux Enterprise Server 10.1 64-Bit + Postgresql-8.2.3 + Python 2.4.4 Any idea, what's going wrong here? Thanks in advance, Nico ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Sorting with DISTINCT ON
It does allow you to sort on both columns. SELECT DISTINCT ON (path) path, comment_id, created, title FROM bewertungen.tblcomments ORDER BY path, created Thank you very much. Works perfect! :-) ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[GENERAL] Sorting with DISTINCT ON
Hi there, I have a problem sorting a SQL result if I use DISTINCT ON. I have a table "tblcomment" with these columns: id (serial) path (varchar) created (timestamp) title (varchar) These records are in the table "tblcomment": id pathcreated title 11 /var/black 2007-01-07 22:17:03.001837 Any title 17 /var/blue 2007-01-07 20:35:55.289713 Any title 13 /var/blue 2007-01-15 15:52:58.438375 Any title 12 /var/green 2007-01-08 19:03:50.470073 Any title 18 /var/red2007-01-07 08:41:47.152676 Any title Now, I want to get all results from this table and if there are duplicates, I want the row whose "created" column has the latest date. In this example, I want to have this result: id pathcreated title 11 /var/black 2007-01-07 22:17:03.001837 Any title 13 /var/blue 2007-01-15 15:52:58.438375 Any title 12 /var/green 2007-01-08 19:03:50.470073 Any title 18 /var/red2007-01-07 08:41:47.152676 Any title My first try was this SQL query: SELECT DISTINCT ON (path) path, comment_id, created, title FROM bewertungen.tblcomments This does not allow me to append "ORDER BY created" since I can only sort on path because of DISTINCT ON (path). My second try was a sub query like this: SELECT comment_id, path, created, title FROM ( SELECT DISTINCT ON (path) path, comment_id, created, title FROM bewertungen.tblcomments ) foo_alias ORDER BY created DESC But this results into: id pathcreated title 11 /var/black 2007-01-07 22:17:03.001837 Any title 17 /var/blue 2007-01-07 20:35:55.289713 Any title 12 /var/green 2007-01-08 19:03:50.470073 Any title 18 /var/red2007-01-07 08:41:47.152676 Any title No matter, if I user ORDER BY created DESC or ORDER BY created ASC. It seems that postgres always takes the first row of the duplicates. In this example: 17 /var/blue 2007-01-07 20:35:55.289713 Any title. Any idea, how I can solve my problem? Regards, Nico ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[GENERAL] Logging in postgresql.conf - no SQL statements are logged
Dear list members, I would like to enable logging and write every SQL command that is executed in a logfile so I modified the "postgresql.conf" file in order to log everything. I restarted the Postgres SQL server. Unfortunately, no SQL statements are logged in the logfile. Here is a snippet of my postgresql.conf: #--- # ERROR REPORTING AND LOGGING #--- # - Where to Log - log_destination = 'stderr' # This is used when logging to stderr: redirect_stderr = true # These are only used if redirect_stderr is on: log_directory = '/data/pgsql/log' log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log' #log_truncate_on_rotation = off log_rotation_age = 1440 #log_rotation_size = 10240 # These are relevant when logging to syslog: #syslog_facility = 'LOCAL0' #syslog_ident = 'postgres' # - When to Log - #client_min_messages = notice ... #--- # RUNTIME STATISTICS #--- Did I miss something? Kind regards, Nico ---(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
[GENERAL] Monitoring Postgres - Get the SQL queries which are sent to postgres
Dear list members, I have a web application running that is connected to my postgres database. This web application builds dynamically SQL queries and queries the postgres database. I would like to see these SQL queries. How can I do this? Is there a way to monitor the SQL queries which are sent to my postgres database? Thanks in advance, Nico ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
[GENERAL] Restarting Slony crashes Postgresql?
Hi group, I'm using Slony-I 1.1.5 with Postgresql 8.1.4 on 3 DB server (OS = debian sarge). I set a replication from a database on server A (master) to 2 servers B and C (slaves). Note that the slaves databases are in production and have a quite big load. I noticed last day that when I stop Slony daemon (for any reason) on a slave server (B or C), a few seconds later, the server doesn't respond anymore. Then I need to reboot the system. I noticed some "fetch 100 from LOG" queries on the master database too... => To prevent the server crashing, It seems that I need to restart postgresql just after restarting Slony (and before it all crashes). Is it always necessary to restart Postgresql after restarting a Slony daemon ? (though, it doesn't seem necessary when the servers have not many connections) Any information will be appreciated. Regards, Nico ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[GENERAL] Foreign key / performance question
Hi !, 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 ? Thanks a lot, Nico Callewaert New Yahoo! Messenger with Voice. Call regular phones from your PC and save big.
Re: [GENERAL] How to reset a sequence so it will start with 1 again?
Take a look at the docs, in particular the three-parameter version of
setval and the is_called flag.
http://www.postgresql.org/docs/current/interactive/functions- sequence.html
Thanks Michael,
SELECT setval('tblperson_id_seq', 1, false);
will do exactly what I supposed to get.
Nico
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
[GENERAL] How to reset a sequence so it will start with 1 again?
Hi there,
I would like to reset a sequence so its id will start with 1 if I insert
a new record into the table, after I have deleted all records from the
table.
I am using Postgres 8.03 and here is what I tried:
test=# create table tblperson (
test(# id SERIAL NOT NULL,
test(# name VARCHAR(200)
test(# );
NOTICE: CREATE TABLE will create implicit sequence "tblperson_id_seq"
for serial column "tblperson.id"
CREATE TABLE
test=# INSERT INTO tblperson (name) VALUES ('John Phelps');
INSERT 27562 1
test=# SELECT * from tblperson;
id |name
+-
1 | John Phelps
(1 row)
test=# SELECT * from tblperson_id_seq;
sequence_name | last_value | increment_by | max_value |
min_value | cache_value | log_cnt | is_cycled | is_called
--++--+-+
tblperson_id_seq | 1 |1 | 9223372036854775807 |
1 | 1 | 0 | f | t
(1 row)
Then, I truncated the table in order to delete all records and insert a
new record to see if it's id will start with 1 - but it starts with 2.
test=# TRUNCATE tblperson;
TRUNCATE TABLE
test=# INSERT INTO tblperson (name) VALUES ('John Phelps');
INSERT 27564 1
test=# SELECT * from tblperson;
id |name
+-
2 | John Phelps
(1 row)
After I truncated tblperson I supposed that the Id will start with 1
again if I insert a new record into tblperson. I thought, truncating the
table tblperson will also reset its sequence "tblperson_id_seq"!? Am I
wrong?
After that, I tried to set the sequence back to 1 since I cannot set the
sequence to 0 using setval() (error: value 0 is out of bounds for
sequence). Unfortunately, setting the sequence back to 1 will start with
id = 2
test=# SELECT setval('tblperson_id_seq', 0);
ERROR: setval: value 0 is out of bounds for sequence "tblperson_id_seq"
(1..9223372036854775807)
test=# SELECT setval('tblperson_id_seq', 1);
setval
1
(1 row)
test=# INSERT INTO tblperson (name) VALUES ('John Phelps');
INSERT 27566 1
test=# SELECT * from tblperson;
id |name
+-
2 | John Phelps
(1 row)
I could do the following, but I don't know if this is a clean solution:
TRUNCATE tblperson;
SELECT setval('tblperson_id_seq', 1);
INSERT INTO tblperson (name) VALUES ('test1');
INSERT INTO tblperson (name) VALUES ('test2');
INSERT INTO tblperson (name) VALUES ('test3');
UPDATE tblperson set id = id-1;
test=# SELECT * from tblperson;
id | name
+---
2 | test2
3 | test3
1 | test
(3 rows)
Any idea, how I can reset the sequence so it will start with 1 again?
Many thanks in advance,
Nico
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
[GENERAL] Best programming language / connectivity for best performance
Hi, I was wondering what is the best database connectivity and programming language to get the best performance with PostgreSQL. I'm currently working with Delphi, but I don't know if that is the best way to go ? Many thanks in advance, Best regards, Nico Callewaert Yahoo! Photos Ring in the New Year with Photo Calendars. Add photos, events, holidays, whatever.
[GENERAL] Re: [GENERAL] Sorting problems with SELECT * FROM table WHERE name LIKE 'Ö %'
I have a problem when sorting records with: SELECT * FROM table WHERE name LIKE 'Ö%' I am running Postgres 8.02 with a database whose character encoding is UNICODE. The SQL Query SELECT * FROM member WHERE name LIKE 'O%' OR name like 'Ö%' ORDER BY name returns this: Öhlmann Öhmann Obenaus Ochoa O'Donovan Oehme Oklant Oltub Oltüch Oltutz Oltüwer According to german sorting rules the result is fine except the both first entries "Öhlmann" and "Öhmann". Why do appear these records at the beginning of the list? The proper result should read like this: Obenaus Ochoa O'Donovan Oehme Öhlmann Öhmann Oklant Oltub Oltüch Oltutz Oltüwer The same problem accours when using "E" where my result is this: Élie de Beaumont Eberer Ecü Edding Emmer The proper result should be: Eberer Ecü Edding Élie de Beaumont Emmer Any idea how I can solve this problem? Thank you very much in advance, Nico To complete the missing information, here are the variables set for the databases: add_missing_fromon archive_command unset australian_timezonesoff authentication_timeout 60 bgwriter_delay 200 bgwriter_maxpages 100 bgwriter_percent1 block_size 8192 check_function_bodies on checkpoint_segments 3 checkpoint_timeout 300 checkpoint_warning 30 client_encoding UNICODE client_min_messages notice commit_delay0 commit_siblings 5 cpu_index_tuple_cost0.001 cpu_operator_cost 0.0025 cpu_tuple_cost 0.01 custom_variable_classes unset DateStyle ISO, MDY db_user_namespace off deadlock_timeout1000 debug_pretty_print off debug_print_parse off debug_print_planoff debug_print_rewritten off debug_shared_buffers0 default_statistics_target 10 default_tablespace unset default_transaction_isolation read committed default_transaction_read_only off default_with_oids on effective_cache_size1000 enable_hashagg on enable_hashjoin on enable_indexscanon enable_mergejoinon enable_nestloop on enable_seqscan on enable_sort on enable_tidscan on explain_pretty_printon extra_float_digits 0 from_collapse_limit 8 fsync on geqoon geqo_effort 5 geqo_generations0 geqo_pool_size 0 geqo_selection_bias 2 geqo_threshold 12 integer_datetimes on join_collapse_limit 8 lc_collate [EMAIL PROTECTED] lc_ctype[EMAIL PROTECTED] lc_messages [EMAIL PROTECTED] lc_monetary [EMAIL PROTECTED] lc_numeric [EMAIL PROTECTED] lc_time [EMAIL PROTECTED] listen_addresseslocalhost log_connections off log_destination stderr log_disconnections off log_durationoff log_error_verbosity default log_executor_stats off log_hostnameoff log_line_prefix unset log_min_duration_statement -1 log_min_error_statement panic log_min_messagesnotice log_parser_statsoff log_planner_stats off log_rotation_age1440 log_rotation_size 10240 log_statement none log_statement_stats off log_truncate_on_rotationoff maintenance_work_mem16384 max_connections 100 max_files_per_process 1000 max_fsm_pages 2 max_fsm_relations 1000 max_function_args 32 max_identifier_length 63 max_index_keys 32 max_locks_per_transaction 64 max_stack_depth 2048 password_encryption on port5432 pre_auth_delay 0 random_page_cost4 redirect_stderr off regex_flavoradvanced rendezvous_name unset search_path $user,public server_encoding UNICODE server_version 8.0.2 shared_buffers 1000 silent_mode off sql_inheritance on ssl off statement_timeout 0 stats_block_level off stats_command_stringoff stats_reset_on_server_start on stats_row_level off stats_start_collector on superuser_reserved_connections 2 syslog_facility LOCAL0 syslog_identpostgres TimeZoneEurope/Berlin trace_notifyoff transaction_isolation read committed transaction_read_only off transform_null_equals off unix_socket_group unset unix_socket_permissions 511 vacuum_cost_delay 0 vacuum_cost_limit 200 vacuum_cost_page_dirty 20 vacuum_cost_page_hit1 vacuum_cost_page_miss 10 wal_buffers 8 wal_sync_method fdatasync work_mem1024 zero_damaged_pages off Is there any explaination why the result is not sorted properly? ---(end of broadcast)--- TIP 6: explain analyze is your friend
[GENERAL] Re: [GENERAL] Sorting problems with SELECT * FROM table WHERE name LIKE 'Ö %'
Ah, I found it: lc_collate: [EMAIL PROTECTED] lc_ctype: [EMAIL PROTECTED] ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[GENERAL] Re: [GENERAL] Sorting problems with SELECT * FROM table WHERE name LIKE 'Ö %'
... but what locale is it using? (See LC_COLLATE and LC_CTYPE.) Can I find out out these settings in "phpPgAdmin"? Or can I use LC_COLLATE and LC_CTYPE in the SQL Query? ---(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
[GENERAL] Sorting problems with SELECT * FROM t able WHERE name LIKE 'Ö%'
Hi there, I have a problem when sorting records with: SELECT * FROM table WHERE name LIKE 'Ö%' I am running Postgres 8.02 with a database whose character encoding is UNICODE. The SQL Query SELECT * FROM member WHERE name LIKE 'O%' OR name like 'Ö%' ORDER BY name returns this: Öhlmann Öhmann Obenaus Ochoa O'Donovan Oehme Oklant Oltub Oltüch Oltutz Oltüwer According to german sorting rules the result is fine except the both first entries "Öhlmann" and "Öhmann". Why do appear these records at the beginning of the list? The proper result should read like this: Obenaus Ochoa O'Donovan Oehme Öhlmann Öhmann Oklant Oltub Oltüch Oltutz Oltüwer The same problem accours when using "E" where my result is this: Élie de Beaumont Eberer Ecü Edding Emmer The proper result should be: Eberer Ecü Edding Élie de Beaumont Emmer Any idea how I can solve this problem? Thank you very much in advance, Nico ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Installation on latest version of Suse Linux
Hi !, Thanks for all the tips Best regards, Nico CallewaertScott Marlowe <[EMAIL PROTECTED]> wrote: On Wed, 2005-06-15 at 16:54, Nico Callewaert wrote:> Hi,> > I'm completely new to PostgreSQL. I don't have any idea how to> install it on a Suse Linux machine. Could somebody provide me> installation instructions ?> > Many thanks in advance,> Insert your suse installation medium, and run your package manager. Then pick all the postgresql packages for installation. After that,read the online docs at www.postgresql.org, especially the stuff at thebeginning of the administration section.__Do You Yahoo!?Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com
[GENERAL] Installation on latest version of Suse Linux
Hi, I'm completely new to PostgreSQL. I don't have any idea how to install it on a Suse Linux machine. Could somebody provide me installation instructions ? Many thanks in advance, Nico Callewaert Discover Yahoo! Have fun online with music videos, cool games, IM & more. Check it out!
Re: [GENERAL] getting inherited table name
Eric Kolve wrote: > In the pgsql tutorial two tables are created capitals inherits cities. > > When you do SELECT * FROM cities, you get both capitals and cities. Is > there anyway to get get the name of the table so I could possibly know > the 'type' it was? Or should this be maintained as a separate column > 'city_type' that has a value of 'capital'? > > Suppose I had another table river_cities and what I would want to be > able to is SELECT * FROM cities and know whether the city was a > river_city, capital, or nothing at all. No additional fields are required: SELECT c.*, c.tableoid, pgc.relname as city_type FROM cities c, pg_class pgc WHERE c.tableoid = pgc.oid regards Nico ---(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] Databases in Belgium
Hello, I'm looking for people in Belgium, who have any experience in working with Databases, so we can exchange views and experiences. Do you know such people (or maybe it's you), please contact me at [EMAIL PROTECTED] . Greetings Nico Vaes ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
[GENERAL] functions for triggers: passing parameters
What is wrong?
CREATE FUNCTION set_value(text) RETURNS OPAQUE AS '
DECLARE
val ALIAS FOR $1;
BEGIN
NEW.inf := val;
RETURN NEW;
END;
' LANGUAGE 'plpgsql';
CREATE TABLE t1 (
id serial,
info text
);
CREATE TRIGGER t1_set_val BEFORE INSERT ON t1
FOR EACH ROW EXECUTE PROCEDURE set_value('some info');
psql 7.1beta5 result:
CREATE
psql:function_for_trigger.sql:15: NOTICE: CREATE TABLE will create implicit
sequence 't1_id_seq' for SERIAL column 't1.id'
psql:function_for_trigger.sql:15: NOTICE: CREATE TABLE/UNIQUE will create
implicit index 't1_id_key' for table 't1'
CREATE
psql:function_for_trigger.sql:17: ERROR: CreateTrigger: function set_value()
does not exist
test=#
(the language plpgsql is already loaded)
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/users-lounge/docs/faq.html
[GENERAL] inheritance and partial index: how to override constraints or default_values
I want to partial index every table in a inherited tree.
Please let me know how to override the fields along inheritance?
===8<=
CREATE SEQUENCE "a_id_seq" increment 1 minvalue 01 maxvalue
01 start 010001 cache 1;
CREATE TABLE "a" (
"id" int4 DEFAULT nextval('a_id_seq'::text) UNIQUE NOT NULL,
"info_a" text,
"created" timestamp DEFAULT CURRENT_TIMESTAMP
);
SELECT * from "a";
CREATE SEQUENCE "b_id_seq" increment 1 minvalue 02 maxvalue
02 start 020001 cache 1;
CREATE TABLE "b" (
"id" int4 DEFAULT nextval('b_id_seq'::text) UNIQUE NOT NULL,
"info_b" text
) inherits ("a");
SELECT * from "b";
psql 7.1beta5 output:
CREATE
psql:test.sql:13: NOTICE: CREATE TABLE/UNIQUE will create implicit index
'a_id_key' for table 'a'
CREATE
id | info_a | created
++-
(0 rows)
CREATE
psql:test.sql:20: NOTICE: CREATE TABLE/UNIQUE will create implicit index
'b_id_key' for table 'b'
psql:test.sql:20: ERROR: CREATE TABLE: attribute "id" already exists in
inherited schema
psql:test.sql:21: ERROR: Relation 'b' does not exist
==8<=
A note for developers: along inheritance if there are two identical attribute
names, IMHO, pg should use the last definition, rather than complaining.
Or better, pg should check if they are of the same type, but however should
always use the last constraint/default_value definition! (I am talking about
father-son inheritance, and not multiple inheritance, where the attribute
name conflict should be dealed separately)
regards, nico
[GENERAL] terminal psql: wrong console keystrokes
Through Konsole, the Up, Down, Left, Right keys work properly, but through psql the keystrokes seem to be wrong: instead of up (for scrolling in the internel buffer) it appears ^[[A Down ^[[B Right ^[[C Left ^[[D Back_del (ok) Forward_del ^[[3~ It happened when I upgraded Mandrake from 7.1 to 7.2 (Linux 2.2.17-21mdksmp i686), postgresql 7.0.2 from Mandrake RPM. Where should I look at? I tried the following keyboard settings: Linux console, xterm, vt100, vt420pc but no-one works properly. thanks!
[GENERAL] Set valued attributes ?
Does anyone know how to make an attribute a collection of OIDs? I haven't found any reference of this object-oriented feature in Postgres online manuals. Please, let me know any hints/links or anything that could help me doing the following: === Assume that each row of tables is identified by OID. I want to define an attribute, say (t1.c2), as a collection of OIDs linking to rows of table t2. At the same time the attribute (t1.c3) contains a collection of OIDs linking to rows of table t3. === How do I do to make the postgres "navigate through OID links", to prevent expensive access methods (joins, sequential or indexed) ?
[GENERAL] ORDBMS vs OODBMS
Hello folks I'm an engineering student and I would be very interested to learn the pros and cons between these two DataBases: - Versant : commercial OODBMS (Object-Oriented) - PostgreSQL : open source ORDBMS (Object-Relational) (Other DB like Oracle are ERDBMS and not ORDBMS if I guess right) It seems to me the main disadvantage of ORDBMS is the still presence of Impendance Mismatch, which is completely absent in OODBMS. Well, I would like to focus the meaning of the "Object" point of view of PostgreSQL: does it offer just the arbitrariness of data structure? What else? Can a row be assimilated to an object ? Please let me know any experiences which can show interesting aspects of PostreSQL vs Versant (and/or other OODBMS in general) ? The good thing in Versant is that it's 100% Object-Oriented and works well with Java (through JVI interface). The sad thing is that it's closed-source and it's very commercial. The good thing in PostgreSQL is that it's open-source and is available everywhere. I hope to find a lot of more good points related to PostgreSQL. Regards Nico
