Re: [GENERAL] Deadlock situation?
"Dan Armbrust" <[EMAIL PROTECTED]> writes: > I had to restart postgres to let things recover - so I can't gather > any more info right now - but if/when it happens again, I'd like to > know what else to gather. Well, there went the evidence :-( ... but what exactly did you have to do to shut it down? I'm wondering whether the backends responded to SIGINT or SIGTERM. Next time, it'd be good to confirm (with top or vmstat or similar) whether the backends are actually idle or are eating CPU or I/O. Also try strace'ing a few of them; the pattern of kernel calls if any would be revealing. The lack of deadlock reports or 't' values in pg_stat_activity.waiting says that you weren't blocking on heavyweight locks. It's not impossible that there was a deadlock at the LWLock level, though. What sort of indexes are there on this table? Teodor just fixed an issue in GIN indexes that involved taking an unreasonable number of LWLocks, and if that code wasn't exposing itself to deadlock risks I'd be pretty surprised. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Character Data Type 'Name'
Andy Anderson <[EMAIL PROTECTED]> writes: > I'm creating my own table of metadata about other tables in my > database. As such, one column will be the names of those other tables, > and the maximum length of the data in this column would be the allowed > length of an identifier. So one possible data type for this column > would be 'varchar(NAMEDATALEN - 1)'. > However, it would seem to be much simpler to use the special character > type 'name', except that the documentation in section 8.3 warns that > "The 'name' type exists only for storage of identifiers in the > internal system catalogs and is not intended for use by the general > user. " > Is there any real harm in using 'name', though, other than lack of > portability? Well, the warning is just there because we don't want anyone whining that we broke their app if we decide to whack around the properties of "name". If you're intentionally tracking PG-specific behavior, though, I think using "name" is perfectly sensible. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Deadlock situation?
Dan Armbrust escribió: > select * from pg_stat_activity;" shows me that most of my connections > in a COMMIT phase: > > 03:05:37.73064-05 | 2008-04-24 03:05:38.419796-05 | 2008-04-24 > 02:11:53.908518-05 | 127.0.0.1 | 53807 > 16385 | ispaaa | 953 |16384 | pslogin | COMMIT | f >| 2008-04-24 Do you have deferred constraints? Maybe some of them are missing indexes. -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support -- 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] Deadlock situation?
Semantics of deadlock aside, I seem to be deadlocked, yet postgres didn't detect any deadlock situation. There are no DDL queries running. Just a lot of updates, and some inserts and deletes. I had to restart postgres to let things recover - so I can't gather any more info right now - but if/when it happens again, I'd like to know what else to gather. Looking at the time stamps, the transaction start timestamp of this autovacuum query is the oldest one: autovacuum: VACUUM public.iphost 2008-04-24 03:05:13.212436-05 | Then, between 03:05:37 and 03:05:38, nearly every other connection came to a halt. A few connections came to a halt several hours later. I'm baffled, because this autovacuum query seems to have locked the entire database. I also don't know what the "waiting" column means in the output - but they all have the flag of "f". Does that column means that it is waiting on a lock - t or f? Thanks, Dan -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Deadlock situation?
On Apr 29, 2008, at 4:54 PM, Dan Armbrust wrote: I have an app that we were load testing - it maintains a pool of connections to PostgreSQL - 8.3.1 Suddenly, after running fine for weeks, the app hung - unable to get a free connection from the pool. select * from pg_stat_activity;" shows me that most of my connections in a COMMIT phase: 03:05:37.73064-05 | 2008-04-24 03:05:38.419796-05 | 2008-04-24 02:11:53.908518-05 | 127.0.0.1 | 53807 16385 | ispaaa | 953 |16384 | pslogin | COMMIT | f | 2008-04-24 While some are in a SELECT: 16385 | ispaaa |1181 |16384 | pslogin | select dynamichos0_.ethernetmacaddr as ethernet1_0_, dynamichos0_.ipaddr as ipaddr0_, dynamichos0_.cpemac as cpemac0_, dynamichos0_.regtime as regtime0_, dynamichos0_.leasetime as leasetime0_, dynamichos0_.last_updated as last5_0_ from iphost dynamichos0_, cpe cpe1_ where dynamichos0_.cpemac=cpe1_.cpemac and 1=1 and dynamichos0_.ethernetmacaddr=$1 and dynamichos0_.cpemac=$2 and cpe1_.regBaseId=$3 and dynamichos0_.ipaddr<>$4| f | 2008-04-24 03:05:37.734041-05 | 2008-04-24 03:05:38.405854-05 | 2008-04-24 02:41:54.413337-05 | 127.0.0.1 | 55363 Perhaps VACUUM had something to do with it?: 16385 | ispaaa |8956 |16384 | pslogin | delete from iphost where leasetime<$1 | f | 2008-04-24 18:43:29.920069-05 | 2008-04-24 18:43:30.116435-05 | 2008-04-24 18:41:59.071032-05 | 127.0.0.1 | 49069 16385 | ispaaa |1618 | 10 | postgres | autovacuum: VACUUM public.iphost | f | 2008-04-24 03:05:13.212436-05 | 2008-04-24 03:05:13.212436-05 | 2008-04-24 03:05:12.526611-05 | | Where should I begin to look for the source of this problem? Thanks for any info, Well, you can look in pg_locks to see if there are outstanding locks waiting on already granted conflicting locks. This isn't a deadlock situation, though, Postgres will detect those and kill one of the offending processes so that the others can finish (it'll leave a log message about it, too). My guess is that you've got some long running write/ddl query that's go a heavy lock on iphost or you have a LOT of queries that need heavy locks hitting the table at once. How large is iphost? How many of those deletes have you got going on it? Do you also have concurrent updates running against it? Do you have any ddl queries running against it (alter tables, index builds/drops, etc...)? Erik Jones DBA | Emma® [EMAIL PROTECTED] 800.595.4401 or 615.292.5888 615.292.0777 (fax) Emma helps organizations everywhere communicate & market in style. Visit us online at http://www.myemma.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Deadlock situation?
I have an app that we were load testing - it maintains a pool of connections to PostgreSQL - 8.3.1 Suddenly, after running fine for weeks, the app hung - unable to get a free connection from the pool. select * from pg_stat_activity;" shows me that most of my connections in a COMMIT phase: 03:05:37.73064-05 | 2008-04-24 03:05:38.419796-05 | 2008-04-24 02:11:53.908518-05 | 127.0.0.1 | 53807 16385 | ispaaa | 953 |16384 | pslogin | COMMIT | f | 2008-04-24 While some are in a SELECT: 16385 | ispaaa |1181 |16384 | pslogin | select dynamichos0_.ethernetmacaddr as ethernet1_0_, dynamichos0_.ipaddr as ipaddr0_, dynamichos0_.cpemac as cpemac0_, dynamichos0_.regtime as regtime0_, dynamichos0_.leasetime as leasetime0_, dynamichos0_.last_updated as last5_0_ from iphost dynamichos0_, cpe cpe1_ where dynamichos0_.cpemac=cpe1_.cpemac and 1=1 and dynamichos0_.ethernetmacaddr=$1 and dynamichos0_.cpemac=$2 and cpe1_.regBaseId=$3 and dynamichos0_.ipaddr<>$4| f | 2008-04-24 03:05:37.734041-05 | 2008-04-24 03:05:38.405854-05 | 2008-04-24 02:41:54.413337-05 | 127.0.0.1 | 55363 Perhaps VACUUM had something to do with it?: 16385 | ispaaa |8956 |16384 | pslogin | delete from iphost where leasetime<$1 | f | 2008-04-24 18:43:29.920069-05 | 2008-04-24 18:43:30.116435-05 | 2008-04-24 18:41:59.071032-05 | 127.0.0.1 | 49069 16385 | ispaaa |1618 | 10 | postgres | autovacuum: VACUUM public.iphost | f | 2008-04-24 03:05:13.212436-05 | 2008-04-24 03:05:13.212436-05 | 2008-04-24 03:05:12.526611-05 | | Where should I begin to look for the source of this problem? Thanks for any info, Dan -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Character Data Type 'Name'
I'm creating my own table of metadata about other tables in my database. As such, one column will be the names of those other tables, and the maximum length of the data in this column would be the allowed length of an identifier. So one possible data type for this column would be 'varchar(NAMEDATALEN - 1)'. However, it would seem to be much simpler to use the special character type 'name', except that the documentation in section 8.3 warns that "The 'name' type exists only for storage of identifiers in the internal system catalogs and is not intended for use by the general user. " Is there any real harm in using 'name', though, other than lack of portability? -- Andy -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Re: passing a temporary table with more than one column to a stored procedure
Hi Valentine,
a little experimentation indicates that an aggregate function can
solve my problem, using an int[] array as the state variable to encode
the computed tuples of the result table so far and then using a costum
function to decode the final returned array from the aggregate into
the table I'm looking for.
I'm afraid though that the SQL aggregate semantics (like having to use
GROUP BY and so on) will get in my way.
I'l let you know,
VIktor
Am 29.04.2008 um 10:30 schrieb valgog:
It looks like you need an aggregate function... but aggregate would
work in case, you want to return a RECORD and not a SETOF RECORD.
In this case, you probably need to operate with arrays. Are you on
8.3? If yes, you would be able to pass an array of type to your
function.
You can accumulate your type-array with array_accum(anyelement)
aggregate (http://www.postgresql.org/docs/8.3/static/xaggr.html)...
but I am not sure about the memory consumption in case of large arrays
being passed to the function.
If you are in the 8.2, you can still convert any type to text, and
deconvert in the function: textin(point_out('(1,1)'::point))
With best regards,
-- Valentine
On Apr 28, 11:52 pm, [EMAIL PROTECTED] (Viktor
Rosenfeld) wrote:
Hi Jon,
Am 28.04.2008 um 19:23 schrieb Roberts, Jon:
What does the signature of graphovertokens look like? Three
parmaters
and it doesn't return a setof?
This is my problem. The return type is setof something (doesn't
really matter), but I don't know what to put into the argument list.
Any ideas?
Viktor
--
Sent via pgsql-general mailing list ([EMAIL PROTECTED])
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
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] psql \pset pager
My fingers sometimes run on "autoappend semicolon" mode and I end up typing "\pset pager always;" instead of "\pset pager always". No error is returned, short (but wide) output is not routed to the pager, and I have to back up and correct the \pset pager command. After some experimentation, I found that any unrecognized string sets the pager to be used for long output: steve=> \pset pager on; Pager is used for long output. steve=> \pset pager off; Pager is used for long output. steve=> \pset pager always; Pager is used for long output. steve=> \pset pager occasionally Pager is used for long output. steve=> \pset pager at random Pager is used for long output. \pset: extra argument "random" ignored The above commands set the pager to be used for long output regardless of the prior setting. Bad input doesn't generate errors except in the case where there are too many parameters. I didn't find this documented. Is the acceptance of bad input by design or an oversight? Also, what would be the feasibility of having psql route output to the pager if the output is too long or too _wide_? I end up with too wide at least as often as too long. Cheers, Steve -- 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] Why is postgres autovacuuming a table that is never updated?
On Tue, Apr 29, 2008 at 02:52:39PM -0400, Joseph S wrote: > I'm running 8.2.6. I have a log table that is always INSERTed to, and no > updates or deletes are ever run on. For some reason the autovacuum decided > it needed to vacuum this table, and it is slowing down my production > server. > > So my questions are: > 1) Why vacuum, if this table is never updated? Do any INSERTs ever fail? If so, you still need to vacuum. They create dead tuples too. Also, every table in every database that accepts connections in your entire cluster (i.e. under one postmaster) MUST be vacuumed once every so many transactions. Autovacuum will notice this in 8.2 and do something about it; the docs say this: "Tables whose relfrozenxid value is more than autovacuum_freeze_max_age transactions old are always vacuumed." > 2) How can I use pg_autovacuum table to disable autovac for this table? > The docs are not clear on how to do this. I think that would be a bad idea, given that autovacuum seems to think you need to do it. Generally you want to alter autovacuum for a table only if autovacuum isn't keeping up. Recheck your assumptions before you do this. (The docs in fact tell you how to do it, but you have to read two parts of the docs to figure it out. I am trying to discourage you from doing what you're planning, so I'm unwilling to tell you how to do it.) A -- Andrew Sullivan [EMAIL PROTECTED] +1 503 667 4564 x104 http://www.commandprompt.com/ -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Why is postgres autovacuuming a table that is never updated?
Joseph S wrote: > I'm running 8.2.6. I have a log table that is always INSERTed to, and > no updates or deletes are ever run on. For some reason the autovacuum > decided it needed to vacuum this table, and it is slowing down my > production server. Perhaps it's because the table is close to Xid wraparound. Please see select age(relfrozenxid) from pg_class where oid = 'your-table'::regclass; If the age exceeds max freeze age, then this is the cause. (The freeze max age can come from the freeze_max_age param, or autovacuum_freeze_max_age, or pg_autovacuum.freeze_max_age). Hmm, maybe it's called max_freeze_age, I don't recall offhand. Beware of the pg_autovacuum column being zero. -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Why is postgres autovacuuming a table that is never updated?
I'm running 8.2.6. I have a log table that is always INSERTed to, and no updates or deletes are ever run on. For some reason the autovacuum decided it needed to vacuum this table, and it is slowing down my production server. So my questions are: 1) Why vacuum, if this table is never updated? 2) How can I use pg_autovacuum table to disable autovac for this table? The docs are not clear on how to do this. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Help! ERROR: could not open relation
Mircea Moisei wrote: I get this strange error Caused by: org.postgresql.util.PSQLException: ERROR: could not open relation 1663/53544/58374: No such file or directory How do I recover from it ? Postgresql version 8.2 on windows. Which update? 8.2.? - newer updates may have fixed the issue. XP or Vista? I think I had an hardware issue in the past where my box rebooted few times I assume this is due to that thing. My guess is it's a good bet. Have you fixed the problem (hardware or virus)? I tried to re index them but is not working. Any ideas ? So postgresql is starting but you get this error when you run a query that should use an index? Or do you get this when you run reindex? Have you tried drop index then create index instead? Is there more you know that would indicate the problem is an index? Can one tell how do I start the server in stand alone mode in windows ? I tried via the postgres command but still can't reindex all... Is the postgres service running? If so you need to stop it before trying the command line. In XP right click My Computer select manage then services. Locate postgres and stop it. I haven't tried on windows but as far as I know you have the same options. From a dos prompt (I would cd to c:\program files\postgresql\8.2\bin) enter postgres --single dbwithproblems -- Shane Ambler pgSQL (at) Sheeky (dot) Biz Get Sheeky @ http://Sheeky.Biz -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] pg_version is missing
was there an explicit definition of $PGDATA variable pointing to the data_old location? rgds, dotyet On Fri, Apr 25, 2008 at 8:47 AM, Roberts, Jon <[EMAIL PROTECTED]> wrote: > I had a problem with a database yesterday on a Windows server. The > service was described as executing "C:\Program > Files\PostgreSQL\8.3\bin\pg_ctl.exe" runservice -w -N "pgsql-8.3" -D > "E:\PostgreSQL\data\". I also had an old backup of the data directory > from 8.2. It was located on E:\PostgreSQL\data_old\. > > I didn't really need to keep the old 8.2 data directory but I did. To > upgrade from 8.2 to 8.3, I had performed an export, un-install 8.2, > install 8.3, and then import. > > So during some cleanup yesterday, I removed the data_old directory and > my database crashed. It complained that it couldn't find pg_version. I > also tried initdb but it wouldn't work. It gave me an error of "the > program 'postgres' is needed by initdb but was not found". > > Any idea on how my new install of 8.3 somehow got linked to the data_old > directory? I'm not asking how to fix the problem because I had a good > backup and I was able to restore. I'm trying to understand why and how > it happened so I can prevent it in the future. > > Maybe this is a Windows problem? We are moving to Solaris by the end of > May which I'm really excited about. PostgreSQL flys on 64 bit Solaris. > > Jon > Author of fn_ugly() :) > > > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general >
Re: [GENERAL] How to modify ENUM datatypes?
On Monday 28 April 2008 17:35, Jeff Davis wrote:
> On Sat, 2008-04-26 at 20:33 -0400, Robert Treat wrote:
> > I think one of the best examples of this is the movie rating system
> > (which I blogged about at
> > http://people.planetpostgresql.org/xzilla/index.php?/archives/320-Postgre
> >SQL-8.3-Features-Enum-Datatype.html )
> >
> > It's a good example of setting pre-defined values that really can
> > leverage the enum types custom ordering. It also showcases the idea of
> > data definitions that "should never change", but that do changes every
> > half dozen years or so. Now you can argue that since it is expected that
> > the ratings might change in some way every few years that an enum type is
> > not a good choice for this, but I feel like some type of counter-argument
> > is that this is probably longer than one would expect thier database
> > software to last. :-)
>
> Let's say you have ratings A, B, and D for 5 years, and then you add
> rating C between B and D.
>
> If you have a constant stream of movies that must be reviewed, then the
> addition of a new rating will necessarily take some fraction of the
> movies away from at least one of the old ratings. In that case, is an
> old B really equal to a new B?
>
> Similar concerns apply to other changes in ENUMs, and for that matter,
> they apply to the FK design, as well.
>
> I would say the *actual* rating is the combination of the rating name,
> and the version of the standards under which it was rated.
>
*You* would say that, but typically movie ratings are not adjusted when a new
rating comes out. For good examples of this, go back and look at 70's era
movies (cowboy movies, war movies, etc...) that are G rated, but have a lot
of people being shot/killed on-screen, something which would give you an
automatic PG rating today. (There are similar issues with PG/R movies in the
80's, typically focused on violence and drug use, before the PG-13 rating
came out).
--
Robert Treat
Build A Brighter LAMP :: Linux Apache {middleware} PostgreSQL
--
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] PITR problem
On Apr 29, 2008, at 3:20 AM, wstrzalka wrote: What is the full pg_standby command string (restore_command=) in your recovery.conf. It sound's like you have pg_standby set to delete archived WALs and possibly have that a little too aggressive. Do you have the -k flag set in your pg_standby call in your restore_command? My restore command is: - restore_command = 'pg_standby -l -d -s 5 -w 0 -t /tmp/ pgsql.promote_trigger.5432 ~postgres/incoming_wal %f %p %r 2>&1 | logger -p local1.info -t pitr-standby' - As you can see I didn't set -k to keep fixed number of WALs, but %r parameter and the PostgreSQL controls number of keeped files automatically (or at least it should) Ok, I hadn't yet set up a standby on 8.3 and so hadn't seen that the %r macro obviates the need for the -k flag. So... The output from pg_standby: Trigger file : /tmp/pgsql.promote_trigger.5432 Waiting for WAL file : 0001.history WAL file path: /var/lib/pgsql/incoming_wal/ 0001.history Restoring to... : pg_xlog/RECOVERYHISTORY Sleep interval : 5 seconds Max wait interval: 0 forever Command for restore : ln -s -f "/var/lib/pgsql/incoming_wal/ 0001.history" "pg_xlog/RECOVERYHISTORY" Keep archive history : 0001000100DB and later running restore : OK Trigger file : /tmp/pgsql.promote_trigger.5432 Waiting for WAL file : 0001000100D9.0020.backup WAL file path: /var/lib/pgsql/incoming_wal/ 0001000100D9.0020.backup Restoring to... : pg_xlog/RECOVERYHISTORY Sleep interval : 5 seconds Max wait interval: 0 forever Command for restore : ln -s -f "/var/lib/pgsql/incoming_wal/ 0001000100D9.0020.backup" "pg_xlog/RECOVERYHISTORY" Keep archive history : 0001000100DB and later running restore : OK Note that here, from the start, postgres is telling the recovery command that it only needs from 0001000100DB and on. Here's where it gets to restoring the first actual log file: Trigger file : /tmp/pgsql.promote_trigger.5432 Waiting for WAL file : 0001000100D9 WAL file path: /var/lib/pgsql/incoming_wal/ 0001000100D9 Restoring to... : pg_xlog/RECOVERYXLOG Sleep interval : 5 seconds Max wait interval: 0 forever Command for restore : ln -s -f "/var/lib/pgsql/incoming_wal/ 0001000100D9" "pg_xlog/RECOVERYXLOG" Keep archive history : 0001000100DB and later running restore : OK removing "/var/lib/pgsql/incoming_wal/0001000100D9" removing "/var/lib/pgsql/incoming_wal/0001000100DA" Since it says 'OK' but then fails my guess is that the order of operations goes something along the lines of this (I could be totally off): 1. Is /var/lib/pgsql/incoming/0001000100D9 present? -> OK 2. Clean up files older than 0001000100DB -> Delete /var/ lib/pgsql/incoming/0001000100D9 and /var/lib/pgsql/ incoming/0001000100DA 3. Restore /var/lib/pgsql/incoming/0001000100D9 -> This is where it breaks. So, the question is: why does does the server say that it only needs 0001000100DB and later? Did you clear out your pg_xlog directory before starting up the standby? Erik Jones DBA | Emma® [EMAIL PROTECTED] 800.595.4401 or 615.292.5888 615.292.0777 (fax) Emma helps organizations everywhere communicate & market in style. Visit us online at http://www.myemma.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] String Comparison and NULL
Andreas Kretschmer <[EMAIL PROTECTED]> writes: >>> ... and I do something like "select id where animal <> 'Cat';" then >>> shouldn't 1, 3, 4 and 5 be picked? As it is I only get 1, 4 and 5. >>> NULL is not 'Cat'. I realize that if I were testing for NULL itself I > NULL is nothing, you can't compare something with nothing. A better way to think about it is that NULL means UNKNOWN. Thus the result of NULL <> 'Cat' is not FALSE but UNKNOWN (ie NULL) --- if you don't know what the value is, you don't know whether or not it's equal to any specific other value. The other mistake novices typically make is to expect that NULL = NULL will yield TRUE. It doesn't, it yields NULL, because again you can't say whether two unknown quantities are equal. You can hack around this behavior to some extent with IS DISTINCT FROM, but generally the right thing is to redesign your data representation. Trying to make NULL act like a normal data value is almost always going to lead to tears in the long run. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] String Comparison and NULL
On Mon, 28 Apr 2008 [EMAIL PROTECTED] wrote: > I'm fairly new to PG and databases in general so this may very well be > a problem in my thought process. > > If I have a simple table with an ID (integer) and Animal (text) like > this... > > 1 Dog > 2 Cat > 3 NULL > 4 Horse > 5 Pig > 6 Cat > 7 Cat > > ... and I do something like "select id where animal <> 'Cat';" then > shouldn't 1, 3, 4 and 5 be picked? Comparisons against null with =, <> and so on return unknown not true or false and WHERE clauses only return rows where the condition is true. You might want to read up on the ternary (three valued) logic and nulls. I haven't read through it but the wikipedia page on null is pretty long. http://en.wikipedia.org/wiki/Null_%28SQL%29 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Sorting nulls and empty strings together
"Andrus" <[EMAIL PROTECTED]> writes: > User interface need to show nulls as empty strings. > PostgreSQL sorts nulls after all data. >... > Select statements are generated dynamically by driver and it is not easy > to change them to generate order by coalesce( testcol,''). You could use NULLS FIRST (assuming your collation has '' sorted at the beginning which I think is normally true). But you would have to switch it to NULLS LAST if you sort descending... > If there is no other way I can change driver to generate coalesce( > testcol,'') as order by expressions. > > However I'm afraid that those order by expression cannot use regular index > like > > create index test_inx on test(testcol) create index test_inx on test(coalesce(testcol,'')) But I bet you'll have trouble using an index at all for the order by. You'll either be searching on other columns which would have to be leading columns of every index or you'll be reading the whole table anyways and postgres will prefer to sort since it's faster. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com Ask me about EnterpriseDB's 24x7 Postgres support! -- 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] Sorting nulls and empty strings together
On Mon, Apr 28, 2008 at 08:05:45PM +0300, Andrus wrote: > User interface need to show nulls as empty strings. > PostgreSQL sorts nulls after all data. > > create temp table test ( testcol char(10) ); > insert into test values ( null); > insert into test values ( 'test'); > insert into test values ( ''); > select * from test order by testcol; > > This confuses users who expect that all empty columns are together in sorted > data. I'd say users are being confused by the assumption the nulls and empty strings are the same when they clearly aren't. Perhaps you should think which of the two you actually want to mean "empty" and then get rid of the other possibility. > If there is no other way I can change driver to generate coalesce( > testcol,'') as order by expressions. > However I'm afraid that those order by expression cannot use regular index > like > > create index test_inx on test(testcol) You could do: create index test_inx on test(coalesce(testcol,'')) Have a nice day, -- Martijn van Oosterhout <[EMAIL PROTECTED]> http://svana.org/kleptog/ > Please line up in a tree and maintain the heap invariant while > boarding. Thank you for flying nlogn airlines. signature.asc Description: Digital signature
Re: [GENERAL] String Comparison and NULL
Pavel Stehule <[EMAIL PROTECTED]> schrieb: > > ... and I do something like "select id where animal <> 'Cat';" then > > shouldn't 1, 3, 4 and 5 be picked? As it is I only get 1, 4 and 5. > > NULL is not 'Cat'. I realize that if I were testing for NULL itself I NULL is nothing, you can't compare something with nothing. As Pavel suggested, As Pavel suggested: > In this case use operator IS DISTINCT FROM > > select id where animal IS DISTINCT FROM 'Cat'; Andreas -- Really, I'm not out to destroy Microsoft. That will just be a completely unintentional side effect. (Linus Torvalds) "If I was god, I would recompile penguin with --enable-fly."(unknow) Kaufbach, Saxony, Germany, Europe. N 51.05082°, E 13.56889° -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] varchar or text
On Tue, Apr 29, 2008 at 09:36:31AM +0200, Pascal Cohen wrote: > I am with 8.3.1 release but I mentioned that this appears with spaces at > then end not with standard chars. Of course your examples are working > fine but insert something like 'abc' (with several spaces and it > will work but just ignore the spaces above the 5th char. Yeah, the SQL standard has some "interesting" features regarding char/varchar and trailing spaces. Text doesn't treat spaces specially at all... Have a nice day, -- Martijn van Oosterhout <[EMAIL PROTECTED]> http://svana.org/kleptog/ > Please line up in a tree and maintain the heap invariant while > boarding. Thank you for flying nlogn airlines. signature.asc Description: Digital signature
Re: [GENERAL] Multibyte (Japanese Character) Sorting
> Hi there, > > Im having a problem in sorting multibyte characters. > > I am using EUC-JP for my database encoding becuase we need to support > japanese (hiragana, katakana, kanji) text, since our clients are japanese. > > I have a table named "user_info" with the following fields: > > first_name character(60) NOT NULL > last_name character(60) NOT NULL > > We've forced doublebyte character our entries so that all data stored in > the table are doublebyte. The problem is, the sorting procedure. when > you user ORDER BY last_name ASC, the list is not sorted properly. Please > help me fix this problem. Thank you in advanced. I'm not sure why you think "not sorted properly", but my wild guess is your OS's locale data is broken. Use C locale. -- Tatsuo Ishii SRA OSS, Inc. Japan -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] String Comparison and NULL
Hello 2008/4/28 <[EMAIL PROTECTED]>: > Hi, > > I'm fairly new to PG and databases in general so this may very well be > a problem in my thought process. > > If I have a simple table with an ID (integer) and Animal (text) like > this... > > 1 Dog > 2 Cat > 3 NULL > 4 Horse > 5 Pig > 6 Cat > 7 Cat > > ... and I do something like "select id where animal <> 'Cat';" then > shouldn't 1, 3, 4 and 5 be picked? As it is I only get 1, 4 and 5. > NULL is not 'Cat'. I realize that if I were testing for NULL itself I > would use IS or IS NOT but this...? I'm a little confused. > In this case use operator IS DISTINCT FROM select id where animal IS DISTINCT FROM 'Cat'; Regards Pavel Stehule > Thanks! > > -- > 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] Delete xml node from xml-document
Hello! I try to delete some node from xml using plpgsql language How can I do it? To sample SELECT INTO xlst_templ '//myNode'; SELECT INTO v_xml XMLPARSE(DOCUMENT value) from myTable WHERE id=ids; RAISE NOTICE '%', v_xml; -- variable v_xml contain my xml document. SELECT INTO v_nodes xpath(xlst_templ, v_xml); -- variable v_nodes contain array of xml elements "myNode" what next? How delete all nodes "myNode'" from document v_xml? Thanks for your help. Oleg A Malyovany
[GENERAL] Sorting nulls and empty strings together
User interface need to show nulls as empty strings. PostgreSQL sorts nulls after all data. create temp table test ( testcol char(10) ); insert into test values ( null); insert into test values ( 'test'); insert into test values ( ''); select * from test order by testcol; This confuses users who expect that all empty columns are together in sorted data. Select statements are generated dynamically by driver and it is not easy to change them to generate order by coalesce( testcol,''). If there is no other way I can change driver to generate coalesce( testcol,'') as order by expressions. However I'm afraid that those order by expression cannot use regular index like create index test_inx on test(testcol) in it thus too slow for large data. How to force PostgreSQL to sort data so that nulls and empty strings appear together ? Andrus. -- 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] PITR problem
> What is the full pg_standby command string (restore_command=) in > your recovery.conf. It sound's like you have pg_standby set to delete > archived WALs and possibly have that a little too aggressive. Do you > have the -k flag set in your pg_standby call in your restore_command? My restore command is: - restore_command = 'pg_standby -l -d -s 5 -w 0 -t /tmp/ pgsql.promote_trigger.5432 ~postgres/incoming_wal %f %p %r 2>&1 | logger -p local1.info -t pitr-standby' - As you can see I didn't set -k to keep fixed number of WALs, but %r parameter and the PostgreSQL controls number of keeped files automatically (or at least it should) -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] question/problem concerning GRANT/REVOKE
hi! I have a pretty basic problem: We have several schemas in one of our databases, and we need the users to see only the tables (and table structure) of tables inside their own schema. So I created schemas for those users, and set their "search_path". But with \d public. users can see all tables (and their structure) in the public (and other) schemas -- and I found no way yet to prevent this?? I have tried "REVOKE ALL" from the database itself, and the other schemas, and single tables -- for the specific user, and also for the user "PUBLIC" -- but it had no effect! How can I prevent exposing the structure of all tables in the database to user that should work in their SCHEMA? Help would be appreciated very much!! :-) cheers, gerhard -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Multibyte (Japanese Character) Sorting
Hi there, Im having a problem in sorting multibyte characters. I am using EUC-JP for my database encoding becuase we need to support japanese (hiragana, katakana, kanji) text, since our clients are japanese. I have a table named "user_info" with the following fields: first_name character(60) NOT NULL last_name character(60) NOT NULL We've forced doublebyte character our entries so that all data stored in the table are doublebyte. The problem is, the sorting procedure. when you user ORDER BY last_name ASC, the list is not sorted properly. Please help me fix this problem. Thank you in advanced. -- == Morgan Gonzales - 1st BU (MSI) - Tsukiden Software There are two kinds of people in this world. One says to God, thy will be done, and the other to whom God says, thy will be done. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Help! ERROR: could not open relation
I get this strange error Caused by: org.postgresql.util.PSQLException: ERROR: could not open relation 1663/53544/58374: No such file or directory How do I recover from it ? Postgresql version 8.2 on windows. I think I had an hardware issue in the past where my box rebooted few times I assume this is due to that thing. I tried to re index them but is not working. Any ideas ? Can one tell how do I start the server in stand alone mode in windows ? I tried via the postgres command but still can't reindex all... Thanks
[GENERAL] String Comparison and NULL
Hi, I'm fairly new to PG and databases in general so this may very well be a problem in my thought process. If I have a simple table with an ID (integer) and Animal (text) like this... 1 Dog 2 Cat 3 NULL 4 Horse 5 Pig 6 Cat 7 Cat ... and I do something like "select id where animal <> 'Cat';" then shouldn't 1, 3, 4 and 5 be picked? As it is I only get 1, 4 and 5. NULL is not 'Cat'. I realize that if I were testing for NULL itself I would use IS or IS NOT but this...? I'm a little confused. Thanks! -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] passing a temporary table with more than one column to a stored procedure
Viktor The quick and dirty method would be to pass the subquery as a string, then execute the subquery in the function. Will T -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Re: passing a temporary table with more than one column to a stored procedure
It looks like you need an aggregate function... but aggregate would
work in case, you want to return a RECORD and not a SETOF RECORD.
In this case, you probably need to operate with arrays. Are you on
8.3? If yes, you would be able to pass an array of type to your
function.
You can accumulate your type-array with array_accum(anyelement)
aggregate (http://www.postgresql.org/docs/8.3/static/xaggr.html)...
but I am not sure about the memory consumption in case of large arrays
being passed to the function.
If you are in the 8.2, you can still convert any type to text, and
deconvert in the function: textin(point_out('(1,1)'::point))
With best regards,
-- Valentine
On Apr 28, 11:52 pm, [EMAIL PROTECTED] (Viktor
Rosenfeld) wrote:
> Hi Jon,
>
> Am 28.04.2008 um 19:23 schrieb Roberts, Jon:
>
> >> What does the signature of graphovertokens look like? Three
> >> parmaters
> > and it doesn't return a setof?
>
> This is my problem. The return type is setof something (doesn't
> really matter), but I don't know what to put into the argument list.
>
> Any ideas?
>
> Viktor
>
> --
> Sent via pgsql-general mailing list ([EMAIL PROTECTED])
> To make changes to your
> subscription:http://www.postgresql.org/mailpref/pgsql-general
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] varchar or text
Guillaume Lelarge wrote:
Pascal Cohen a écrit :
I had a look in previous posts in the forum but could not find the
answer I was looking for.
My question is should I switch from varchar to text.
We have "discovered" although it seems to be SQL that adding
something like 'text ' to a varchar(50) just silently
cut the text while a text with check(length) - or also a varchar with
a check raised an error.
Nope. If you try to add some text with more than 50 characters on a
varchar(50) column, you will get an error. For example :
test=# create table t (c varchar(5));
CREATE TABLE
test=# insert into t (c) values ('12345');
INSERT 0 1
test=# insert into t (c) values ('123456');
ERREUR: valeur trop longue pour le type character varying(5)
(the english error message is:
ERROR: value too long for type character varying(5)
).
Which release do you use ?
I am with 8.3.1 release but I mentioned that this appears with spaces at
then end not with standard chars. Of course your examples are working
fine but insert something like 'abc' (with several spaces and it
will work but just ignore the spaces above the 5th char.
--
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] close database, nomount state
[EMAIL PROTECTED] wrote: Hello, I want to ask if there is something like nomount state or close database state in which I can acces postgresql to drop database or to do some other stuff. Because when there are some connections, drop database is not possible. Or is this done some other way? Lukas Houf Short answer-- no. Longer answer-- there's really no need for the Oracle-esque nomount state in Pg. If you're doing media recovery, it's very much all or nothing, cluster-wide. You are not going to do media recovery for a set of tablespaces, for example. If you'd like to drop a database, you can cut off connections (say, via pg_hba.conf or whatever floats your boat) and drop it with a single command. It's not such a big deal as it is in Oracle. If this doesn't answer your question, could you say more about what your issue is? Regards, Paul -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
