Re: [BUGS] BUG #3270: limit < 16 optimizer behaviour
This should have been asked on the performance list, not filed as a bug. I doubt anyone will have a complete answer to your question without EXPLAIN ANALYZE output from the query. Have you ANALYZE'd the tables recently? Poor statistics is one possible cause of the issue you are having. On Fri, May 11, 2007 at 14:07:57 +, Liviu Ionescu <[EMAIL PROTECTED]> wrote: > > The following bug has been logged online: > > Bug reference: 3270 > Logged by: Liviu Ionescu > Email address: [EMAIL PROTECTED] > PostgreSQL version: 8.2.4 > Operating system: Linux > Description:limit < 16 optimizer behaviour > Details: > > I have a table of about 15Mrows, and a query like this: > > SELECT historianid,storagedate,slotdate,status,value FROM historiandata > JOIN rtunodes ON(historiandata.historianid=rtunodes.nodeid) > JOIN rtus ON(rtunodes.rtuid=rtus.nodeid) > WHERE realmid IN (1119,1422,698,1428) > AND historianid in (2996) > ORDER BY storagedate desc > LIMIT 10 > > if there are no records with the given historianid, if limit is >= 16 the > query is quite fast, otherwise it takes forever. > > my current fix was to always increase the limit to 16, but, although I know > the optimizer behaviour depends on LIMIT, I still feel this looks like a > bug; if the resultset has no records the value of the LIMIT should not > matter. > > regards, > > Liviu Ionescu > > > > CREATE TABLE historiandata > ( > historianid int4 NOT NULL, > status int2 NOT NULL DEFAULT 0, > value float8, > slotdate timestamptz NOT NULL, > storagedate timestamptz NOT NULL DEFAULT now(), > CONSTRAINT historiandata_pkey PRIMARY KEY (historianid, slotdate), > CONSTRAINT historianid_fkey FOREIGN KEY (historianid) > REFERENCES historians (nodeid) MATCH SIMPLE > ON UPDATE CASCADE ON DELETE RESTRICT > ) > WITHOUT OIDS; > ALTER TABLE historiandata OWNER TO tomcat; > > > -- Index: historiandata_historianid_index > > -- DROP INDEX historiandata_historianid_index; > > CREATE INDEX historiandata_historianid_index > ON historiandata > USING btree > (historianid); > > -- Index: historiandata_slotdate_index > > -- DROP INDEX historiandata_slotdate_index; > > CREATE INDEX historiandata_slotdate_index > ON historiandata > USING btree > (slotdate); > > -- Index: historiandata_storagedate_index > > -- DROP INDEX historiandata_storagedate_index; > > CREATE INDEX historiandata_storagedate_index > ON historiandata > USING btree > (storagedate); > > > CREATE TABLE rtunodes > ( > nodeid int4 NOT NULL, > rtuid int4 NOT NULL, > no_publicnodeid int4, > name varchar(64) NOT NULL, > isinvalid bool NOT NULL DEFAULT false, > nodetype varchar(16), > CONSTRAINT rtunodes_pkey PRIMARY KEY (nodeid), > CONSTRAINT nodeid_fkey FOREIGN KEY (nodeid) > REFERENCES nodes (nodeid) MATCH SIMPLE > ON UPDATE CASCADE ON DELETE RESTRICT, > CONSTRAINT rtuid_fkey FOREIGN KEY (rtuid) > REFERENCES rtus (nodeid) MATCH SIMPLE > ON UPDATE CASCADE ON DELETE RESTRICT > ) > WITHOUT OIDS; > ALTER TABLE rtunodes OWNER TO tomcat; > > > > CREATE TABLE rtus > ( > nodeid int4 NOT NULL, > passwd varchar(10) NOT NULL, > xml text, > no_nextpublicnodeid int4 NOT NULL DEFAULT 1, > rtudriverid int2, > realmid int4 NOT NULL, > enablegetlogin bool NOT NULL DEFAULT false, > enablegetconfig bool NOT NULL DEFAULT false, > businfoxml text, > uniqueid varchar(32) NOT NULL, > no_publicrtuid int4, > loginname varchar(10) NOT NULL, > protocolversion varchar(8) DEFAULT '0.0'::character varying, > isinvalid bool DEFAULT false, > CONSTRAINT rtus_pkey PRIMARY KEY (nodeid), > CONSTRAINT nodeid_fkey FOREIGN KEY (nodeid) > REFERENCES nodes (nodeid) MATCH SIMPLE > ON UPDATE CASCADE ON DELETE RESTRICT, > CONSTRAINT realmid_fkey FOREIGN KEY (realmid) > REFERENCES realms (nodeid) MATCH SIMPLE > ON UPDATE CASCADE ON DELETE RESTRICT, > CONSTRAINT rtudriverid_fkey FOREIGN KEY (rtudriverid) > REFERENCES rtudrivers (rtudriverid) MATCH SIMPLE > ON UPDATE CASCADE ON DELETE RESTRICT, > CONSTRAINT rtus_loginname_unique UNIQUE (loginname), > CONSTRAINT rtus_uniqueid_unique UNIQUE (uniqueid) > ) > WITHOUT OIDS; > ALTER TABLE rtus OWNER TO tomcat; > > > -- Index: rtus_realmid_index > > -- DROP INDEX rtus_realmid_index; > > CREATE INDEX rtus_realmid_index > ON rtus > USING btree > (realmid); > > -- Index: rtus_rtudriverid_index > > -- DROP INDEX rtus_rtudriverid_index; > > CREATE INDEX rtus_rtudriverid_index > ON rtus > USING btree > (rtudriverid); > > ---(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 ---(end of broadcast)--- TIP 4: Have you searched our l
Re: [BUGS] BUG #2802: Feature request: tinyint and unsigned types
On Fri, Dec 01, 2006 at 20:41:32 +, Albert Strasheim <[EMAIL PROTECTED]> wrote: > > Description:Feature request: tinyint and unsigned types You can already use "char" to store 1 byte values, though unless there are several of these in a row, you won't save any space because of alignment. There are also people working on a nicer way to do enum, that looked to be relatively space efficient (though I don't remember if there was going to be a way to store small sets using only one byte). This got discussed a while ago, but didn't make it into 8.2 (as far as I can tell). Depending on what you are really trying to do, bit strings may also be useful for you. While individual bit string columns aren't going to fit in one byte, you can pack bits together which might be a saving over all. Postgres is extensible, so you can add your one 1 byte types without doing too much work. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [BUGS] BUG #2789: problem with delete statement
On Tue, Nov 28, 2006 at 00:21:27 +, Lucian Capdefier <[EMAIL PROTECTED]> wrote: > > 1. I cannot user table alias in the FROM clause of the DELETE statement. That isn't in the SQL spec, but the extension to allow it has been added to 8.2 which will be released very shortly. > 2. I cannot user more than one, table in the FROM clause. The FROM clause lists the (single) table that rows are to be deleted from. If you want to join to other tables you should use the USING clause. USING was added a release or two ago, so if you have a really old release, if might not be available there. The solution in that case is to take advantage of the "missing from" feature and just use the table without giving the table name in a FROM or USING clause. For questions like this you are going to be better off asking on the general or novice lists instead of bugs. Less people are going to see the message on the bugs list, so you are less likely to get help with posts there. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [BUGS] BUG #2784: Performance serious degrades over a period of a month
This really should have been asked on pgsql-performance and would probably get a better response there.. On Sun, Nov 26, 2006 at 16:35:52 +, Michael Simms <[EMAIL PROTECTED]> wrote: > PostgreSQL version: 8.1.4 > Operating system: Linux kernel 2.6.12 > Description:Performance serious degrades over a period of a month > Details: > > OK, we have a database that runs perfectly well after a dump and restore, > but over a period of a month or two, it just degrades to the point of > uselessness. > vacuumdb -a is run every 24 hours. We have also run for months at a time > using -a -z but the effect doesnt change. > This sounds like you either need to increase your FSM setting or vacuum more often. I think vacuumdb -v will give you enough information to tell if FSM is too low at the frequency you are vacuuming. > The database is for a counter, not the most critical part of the system, but > a part of the system nonetheless. Other tables we have also degrade over > time, but the counter is the most pronounced. There seems to be no common > feature of the tables that degrade. All I know is that a series of queries > that are run on the database every 24 hours, after a dump/restore takes 2 > hours. Now, 2 months after, it is taking over 12. We are seriously > considering switching to mysql to avoid this issue. You probably will want to vacuum the counter table more often than the other tables in the database. Depending on how often the counter(s) are being updated and how many separate counters are in the table you might want to vacuum that table as often as once a minute. Depending on your requirements you might also want to consider using a sequence instead of a table row for the counter. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [BUGS] BUG #2772: Undefined Subroutine Pg::connectdb (" ");
This isn't a bug and you should have asked on a different list. I am going to move this over to pgsql-general. On Mon, Nov 20, 2006 at 12:07:25 +, "S.Balaji" <[EMAIL PROTECTED]> wrote: > > In Redhat 7.2 use Pg; command in perl Scripts will working with out any > Problem.But in CentOS use Pg command will not Working it shows error is > cannot Locate Pg.pm in Include Directory.After I am Solved this Problem by > export PERLLIB=/usr/lib/perl5/vendor_perl/5.8.5/i386-linux-thread-multi/DBD/ > command.After it shows error is undefined subroutine Pg::connectdb > command.how can i solve this Problem without changing the use Pg command or > any other equivalent command is there.Please help me to solve this Problem The Pg perl module is not getting much support, but you can get a copy of the source from ftp://gborg.postgresql.org/pub/pgperl/stable/Pg-2.1.1.tar.gz and build it yourself. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [BUGS] Sugestion for PostgreSQ
On Fri, Nov 03, 2006 at 09:50:01 -0200, Marcio <[EMAIL PROTECTED]> wrote: > I have a sugestion for pgsql 8.2 or next versions ... > Is complicated encrypt the procedures/functions on Database like Oracle or > similar? > I sugest this because we develop ERP/MRP with complex routines and we like > hide all of this routines...this is our "business secret". > I know if we write this code in C, for exampleis possible to hide. But, > we prefer pl/pgsql native. > Thank´s, One solution is to run the database and the application that connects to it on a machine your business controls and only allow customers to connect to the application (and not directly to the database). Another is to have a contract that limits what your customers can do with the information gained from seeing your propietary code. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [BUGS] BUG #2658: Query not using index
This shouldn't have been submitted to the bugs list, as it isn't a bug. The best spot for this kind of question is the performance list so I am copying it there and redirecting followups there. On Wed, Sep 27, 2006 at 20:56:32 +, Graham Davis <[EMAIL PROTECTED]> wrote: > > SELECT assetid, max(ts) AS ts > FROM asset_positions > GROUP BY assetid; > > I have an index on (ts), another index on (assetid) and a multikey index on > (assetid, ts). I know the assetid index is pointless since the multikey one > takes its place, but I put it there while testing just to make sure. The > ANALYZE EXPLAIN for this query is: > > QUERY PLAN > > - > HashAggregate (cost=125423.96..125424.21 rows=20 width=12) (actual > time=39693.995..39694.036 rows=20 loops=1) >-> Seq Scan on asset_positions (cost=0.00..116654.64 rows=1753864 > width=12) (actual time=20002.362..34724.896 rows=1738693 loops=1) > Total runtime: 39694.245 ms > (3 rows) > > You can see it is doing a sequential scan on the table when it should be > using the (assetid, ts) index, or at the very least the (ts) index. This > query takes about 40 seconds to complete with a table of 1.7 million rows. > I tested running the query without the group by as follows: > SELECT DISTINCT ON (assetid) assetid, ts > FROM asset_positions > ORDER BY assetid, ts DESC; This is almost what you want to do to get an alternative plan. But you need to ORDER BY assetid DESC, ts DESC to make use of the multicolumn index. If you really need the other output order, reverse it in your application or use the above as a subselect in another query that orders by assetid ASC. ---(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: [BUGS] BUG #2648: I can not create table name with capital and small characters
On Mon, Sep 25, 2006 at 14:59:46 +0200, Harald Armin Massa <[EMAIL PROTECTED]> wrote: > Asaf, > > SQL-92 states that identifiers [for dictionary objects] are limited to 128 > characters and are case-insensitive (unless delimited by double quotes), > because they are automatically translated into uppercase by the system. Note that Postgres actually lowercases unquoted names instead of uppercasing them. This difference can be significant. ---(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: [BUGS] Partially corrupted table
On Wed, Aug 30, 2006 at 10:18:46 +0200, Filip Hrbek <[EMAIL PROTECTED]> wrote: > > It is possible that the corruption was caused by a HW problem at customer's > server, and then this problem appeared also at our development environment > because of the data already beeing corrupted. I will recommend the customer > to make some memory tests. You might also ask them if they are using ECC memory. A single bit flip could have easily happened with good memory that didn't have error correction. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [BUGS] BUG #2543: Performance delay acrros the same day
On Fri, Jul 21, 2006 at 07:41:02 +, Alaa El Gohary <[EMAIL PROTECTED]> wrote: > > The following bug has been logged online: The report below isn't a bug, its a performance question and should have been sent to [EMAIL PROTECTED] I am redirecting replies there. > A query on the postgresql DB takes about 5 seconds and then it starts to > take more time till it reaches about 60 seconds by the end of the same day. > I tried vacuum but nothing changed the only thing that works is to dump the > DB ,drop and create a new one with the dump taken. > i need to know if there is any way to restore the performance back without > the need for drop and create > cause i can't do this accross the day You most likely aren't vacuuming often enough and/or don't have your FSM setting high enough. ---(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: [BUGS] BUG #2469: Error in connection to the server
Please don't use the bug reporting facilities to ask support questions. Your question would be appropiate for either the pgsql-general@postgresql.org or [EMAIL PROTECTED] mailing lists. ---(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: [BUGS] BUG #2468: how to create auto-generate primary key
Please don't use the bug reporting facilities to ask support questions. pgsql-general@postgresql.org would be a reasonable list to ask your question. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [BUGS] BUG #2418: number & date & time
On Tue, May 02, 2006 at 10:36:52 +, James <[EMAIL PROTECTED]> wrote: > > we would like to have convinience data conversion from datatype number of > value '' as null and datatype date / timestamp / timestampz of value '' as > null... > > so far, postgresql will reject those value for number and date / time field > when it's empty string '' (as invalid date value or invalid number value) > > it would be nice to have it auto converted :) This isn't a bug and you should have asked this question on another list (sql, general or novice would all be appropiate). This isn't easy for you to do in Postgres as data validation is done before before triggers activate. I can think of a couple of things to do. One would be to change your app or add a filter so that NULL is sent instead of ''. Another apporach would be to read in the data as text with a function that converts the string to date with the special feature of '' being converted to null. Your function should be able to call the function used to input dates, so you don't need to do a lot of hard work there. But depending on how you are inputting the data, it may not be easy to make this fit an existing app. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [BUGS] BUG #2417: bug for finding string in column
On Tue, May 02, 2006 at 10:34:12 +, James <[EMAIL PROTECTED]> wrote: > > i've encounter bug where when a field is empty / null (type varchar(255) ), > when i use the sql query <> 'myvalue', it seems to be false all the time. Empty and NULL are not the same thing. NULL <> to any value evaluates to NULL which will not satisfy a WHERE clause. This is according to the SQL (i.e. it's not a bug). ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [BUGS] BUG #2403: Date arithemtic using INTERVAL in UPDATE command does not work
On Sat, Apr 22, 2006 at 20:34:03 +, "Harry E. Clarke" <[EMAIL PROTECTED]> wrote: > > The following bug has been logged online: > > Bug reference: 2403 > Logged by: Harry E. Clarke > Email address: [EMAIL PROTECTED] > PostgreSQL version: 8.1.3 > Operating system: Suse Linux 10.0 > Description:Date arithemtic using INTERVAL in UPDATE command does > not work > Details: > > Entering the command > > UPDATE table SET col_date = col_date - INTERVAL '100' YEAR where condition; > > does not perform any date aritimetic, and thus the date value in col_date > remains unchanged. col_date contains a value such as '2039-12-07'. If the > command > > UPDATE table SET col_date = DATE '1939-12-07' where condition; > > is entered, the command executes as expected. You probably just made a mistake with the condition. But since you haven't shown us what it was, we can't give you any specific advice about it. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [BUGS] BUG #2292: Calling conventions in docs
On Wed, Mar 01, 2006 at 13:08:17 +, Adriaan van Os <[EMAIL PROTECTED]> wrote: > > Section 32.9.5. Writing Code of the 8.1 docs says: > > >>While it may be possible to load functions written in languages other than > C into PostgreSQL, this is usually difficult (when it is possible at all) > because other languages, such as C++, FORTRAN, or Pascal often do not follow > the same calling convention as C. That is, other languages do not pass > argument and return values between functions in the same way<< > > This is nonsense. GNU Pascal and GNU C use the same calling conventions. In > fact, they share the same compiler-backend. You seem to be assuming that Postgres will always be built using gcc. That isn't the case. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [BUGS] BUG #2256: install problem
On Mon, Feb 13, 2006 at 11:41:24 +, maoke <[EMAIL PROTECTED]> wrote: > > The following bug has been logged online: > > Bug reference: 2256 > Logged by: maoke > Email address: [EMAIL PROTECTED] > PostgreSQL version: 8.1.2 > Operating system: LINUX > Description:install problem > Details: > > hi,master > how to install PostgreSQL8.1.2 to linux? > thanks. > This is not a bug and should have been asked on the appropiate list. It really is a question for your Linux distributor, but if you repost to pgsql-novice stating which linux distribution you are using, someone can probably help you. ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [BUGS] BUG #2243: Postgresql fails to finish some queries
On Tue, Feb 07, 2006 at 02:41:03 +, Matej Rizman <[EMAIL PROTECTED]> wrote: > > Files a_3.txt and b_3.txt contain 3 numbers each. > > The last query (SELECT) is executed on my machine in 125ms. > > If I load data from files a_10.txt and b_10.txt that contain 10 > numbers each, the last SELECT does not finish in more than ten minutes (the > real-world sample hasn't finished in more than an hour). Did you do an ANALYZE after doing the load? ---(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: [BUGS] BUG #2225: Backend crash -- BIG table
On Fri, Feb 03, 2006 at 19:38:04 +0100, Patrick Rotsaert <[EMAIL PROTECTED]> wrote: > > I have 5.1GB of free disk space. If this is the cause, I have a > problem... or is there another way to extract (and remove) duplicate rows? How about processing a subset of the ids in one pass and then may make multiple passes to check all of the ids. As long as you don't have to use too small of chunks, this might work for you. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [BUGS] BUG #2225: Backend crash -- BIG table
On Wed, Feb 01, 2006 at 17:57:50 +0100, Patrick Rotsaert <[EMAIL PROTECTED]> wrote: > I did read it, very carefully. The proposed fix will only work in 2.6 > kernels. Mine is a 2.4 and upgrading it is not an option. The document > suggests to look at the kernel source for 2.4 kernels. I did that, as I > wrote in the previous mail. Setting the overcommit parameter to '2', or > any value for that matter, won't do any good because in this kernel, it > is only tested if it is non-zero. On my system, the parameter is 0, so > overcommit is *not* enabled. I don't know what else I can do. > The other proposed option is to install more memory. Sorry, not an > option, 1GB has to be sufficient. Is there some reason you can't add more swap space? > Apart from the overcommit subject, why is postgres consuming so much > memory? Should the solution of this problem not be searched for here? How do you know it is Postgres that is using lots of memory? The OOM killer doesn't just kill of memory hogs, so you can't just assume the processes being killed tells you which processes were using lots of memory. The memory that Postgres uses is controlled in postgresql.conf. One particular gotcha is that sortmem is per sort, so if you have a number of concurrent sorts you might be using more memory than you expected. ---(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: [BUGS] BUG #2105: ERROR: relation "comoma" does not exist
On Fri, Dec 09, 2005 at 18:09:52 +,
alejandro ramirez <[EMAIL PROTECTED]> wrote:
>
> The following bug has been logged online:
>
> Bug reference: 2105
> Logged by: alejandro ramirez
> Email address: [EMAIL PROTECTED]
> PostgreSQL version: PostgreSQL 8.1.
> Operating system: windows 2000 profesional
> Description:ERROR: relation "comoma" does not exist
> Details:
>
> Hi there!
> i a new user of PostgreSQL, i develop on severals languajes like as
> J2SDK/J2EE/SERVLETS/VB/FOXPRO...etc
> and i use a several RDBM. I try to use PostgreSQL to implement into 2
> aplication's (one on the internet and the other in aq model two fase's like
> a client-server), but i meet a problem when i use a table for a simple
> "SELECT" or "DELET FROM myTable" the result for this is: "ERROR: relation
> "comoma" does not exist" (comoma it's a table) but this table exists on my
> database named "AMS"
>
> i looking for a this error on the net, but i don't found any answer...
Did you try to get a list of tables in psql using the \d command?
>
> please, do you know what happened with this!
Most likely you created the table's name with an uppercase character and now
need to quote (") the table name whenever you use it.
The normal rule is to either never (preferred) quote table names or always
quote table names. Doing it sometimes is a recipie from headaches.
>
> for you help, than's a lote!
You shouldn't have reported this as a bug as your first step. This probably
should have been posted to the novice or general lists.
>
> Regards from mexico
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
Re: [BUGS] BUG #2088: logfiles only readable by instance owner
On Fri, Dec 02, 2005 at 23:30:21 +0100, Dick Snippe <[EMAIL PROTECTED]> wrote: > > Using a log rotation program is possible (apache rotatelogs comes to mind), > but that would require starting postgresql with something like > postmaster | rotatelogs > and just hope that rotetelogs doesn't die in the weeks or months that > postmaster runs. Also postmaster can't be started as a daemon in this case. You can use multilog in DJB's daemontools package to do this. It writes the logfiles publicly readable, so you control access to them via the directory they are contained in. ---(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: [BUGS] On-line backup
On Tue, Nov 29, 2005 at 01:21:12 +0100,
Diego Cattelan <[EMAIL PROTECTED]> wrote:
>
> >What exact commands are you using for both the backup and the restore?
> >
> - at boot time the linux box mount a smb share '/mnt/walarchive810'
> - I have modified postgresql.conf parameter
> archive_command='cp -i /mnt/walarchive810/%f - restarting postgres shows that wal files are correctly archived
> - install postgres 8.1 on win 2000 server and test if work: ok
> - stop postgres set postgres service start mode to manual
> - connect to postgres on linux
> - issue command select ps_start_backup('contab')
> - used tar to archive the data folder as database.tar.gz
> - select pg_stop_backup()
> - copy database.tar.gz on windows
I don't think you can count on linux and windows versions on postgres being
binary compatible.
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
Re: [BUGS] BUG #2037: user function call unexpected "input out of range"
On Fri, Nov 11, 2005 at 21:26:47 +, Tom <[EMAIL PROTECTED]> wrote: > > --Calculate distance > select acos > ( > sin($1*pi()/180)*sin($3*pi()/180) > + > cos($1*pi()/180)*cos($3*pi()/180) > *cos(($2-$4)*pi()/180) > )*60*1.1515*180/pi(); In addition to the other comments, you don't want to calculate distance this way. It isn't very accurate when $2 and $4 are nearly equal which is the normal case. Do a search for haversine. ---(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: [BUGS] BUG #2021: Now able to download Postgresql 8.0.4
On Sat, Nov 05, 2005 at 04:21:05 +, pritesh <[EMAIL PROTECTED]> wrote: > > The following bug has been logged online: > > Bug reference: 2021 > Logged by: pritesh > Email address: [EMAIL PROTECTED] > PostgreSQL version: 8.0.4 > Operating system: Windows XP > Description:Now able to download Postgresql 8.0.4 > Details: > > Hello > To whomsoever it may concern I just want to let you know that I am not able > to download postgresql 8.0.4 for windows from your ftp sire and bittorent > site. So please provide me another site from where I can download it. > Thank you > Pritesh Patel Where did you try looking? It seems to be at: http://www.postgresql.org/ftp/binary/v8.0.4/win32/ Depending on what you are doing you may want to go right to 8.1 though. ---(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: [BUGS] i'm unable to download psql servel
On Fri, Oct 21, 2005 at 12:58:01 +0530, RaghuNath <[EMAIL PROTECTED]> wrote: > hello, > How can i get PostgreSQL 7.4.6.. > please send reply Its on the download page (http://www.postgresql.org/ftp/), but you probably better off using the latest 7.4.x version (currently 7.4.9) if you need to use a version in that branch. ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [BUGS] BUG #1943: Lock A row with the option NoWait
On Thu, Oct 06, 2005 at 17:42:25 +0100, Mathias Laurent <[EMAIL PROTECTED]> wrote: > > The following bug has been logged online: > > Bug reference: 1943 > Logged by: Mathias Laurent > Email address: [EMAIL PROTECTED] > PostgreSQL version: 8.0 > Operating system: windows > Description:Lock A row with the option NoWait > Details: > > Hello, > I would like to know how to make a lock on a row with the instruction > NoWait. Because it is not possible with "Select For Update" which don't > allow NoWait Behind ! > Also if it is not possible to do this thing, could it be develloped in a > next version ? This will be a new feature in 8.1. See the 8.1 release notes: http://candle.pha.pa.us/main/writings/pgsql/sgml/release.html ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [BUGS] BUG #1854: SQL Bug
On Mon, Aug 29, 2005 at 09:11:06 -0700, Stephan Szabo <[EMAIL PROTECTED]> wrote: > > On Mon, 29 Aug 2005, Christian Almeida wrote: > > > Should this sql work? > > Yes and no. ;) Whether this errors or adds the reference is controlled by > a configuration variable, add_missing_from. For backwards compatibility > reasons (and for allowing outside table references in delete) the variable > currently defaults to on IIRC. At some point in the future the default > will likely change to off. Unless things change it will be off by default in 8.1. This is mentioned in the release notes. There is now a USING clause for DELETE which covers the case that couldn't previously be handled without the missing from feature. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [BUGS] BUG #1830: Non-super-user must be able to copy from a file
On Wed, Aug 17, 2005 at 09:22:16 +0100, Bernard <[EMAIL PROTECTED]> wrote: > > The following bug has been logged online: This isn't a bug and you really should have asked this question on another list. I am moving the discussion over to the general list. > > Bug reference: 1830 > Logged by: Bernard > Email address: [EMAIL PROTECTED] > PostgreSQL version: 8.0.3 > Operating system: Linux RedHat 9 > Description:Non-super-user must be able to copy from a file > Details: > > On the attempt to bulk load a table from a file that is owned by the > non-superuser current database user, the following error message is > printed: > > "must be superuser to COPY to or from a file" > > What is the reason for this limitation? This is described in the documentation for the copy command. > > It can't justifiably be for security reasons because if a web application > such as tomcat requires to bulk load tables automatically on a regular basis > then one would be forced to let the web application connect as superuser, > which is very bad for security. No, because you can have the app read the file and then pass the data to the copy command. To do this you use STDIN as the file name. > > In MySQL bulk loading works for all users. You can use the \copy command in psql to load data from files. > > We need a Postgresql solution. > > We have a web application where both MySQL and Postresql are supported. With > Postgresql, the application would have to connect as user postgres. We have > to explain this security risk to our clients very clearly. > > ---(end of broadcast)--- > TIP 2: Don't 'kill -9' the postmaster ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [BUGS] BUG #1797: Problem using Limit in a function, seqscan
On Fri, Jul 29, 2005 at 13:52:45 +0100, Magno Leite <[EMAIL PROTECTED]> wrote: > > Description:Problem using Limit in a function, seqscan > > I looked for about this problem in BUG REPORT but I can't find. This is my > problem, when I try to use limit in a function, the Postgre doesn't use my > index, then it use sequencial scan. What is the problem ? You haven't described the problem well enough to allow us to help you and you posted it to the wrong list. This should be discussed on the performance list, not the bug list. It would help if you showed us the query you are running and run it outside of the function with EXPLAIN ANALYSE and show us that output. Depending on what that output shows, we may ask you other questions. ---(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: [BUGS] BUG #1775: UTF8 to ISO does not convert the german "sharp s" (ß)
On Wed, Jul 20, 2005 at 08:35:33 +1000, John Hansen <[EMAIL PROTECTED]> wrote: > > ICU support should make it to 8.1 I hear, which will make the bahaviour > uniform. There was some discussion about that, but my impression was that that wasn't going to happen. ---(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: [BUGS] BUG #1697: Select getting slower on continously updating data
On Fri, Jun 03, 2005 at 00:09:00 -0700, Bahadur Singh <[EMAIL PROTECTED]> wrote: > > Many thanks for this tip ! > But is this good idea to analyse/vacuuming the > database tables while updates are taking place.. > Since, I update continuously say (100,000 ) times or > more the same data set. > > This is the result of analyze command. > > INFO: analyzing "public.salesarticle" > INFO: "salesarticle": scanned 3000 of 20850 pages, > containing 62 live rows and 134938 dead rows; 62 rows > in sample, 431 estimated total rows > > Gesamtlaufzeit der Abfrage: 5531 ms. > Total Time Taken : 5531 ms. > > Can you suggest me some clever way to so, because I > would prefer to do vaccumming while database is not > loaded with queries/transactions. While that may be a nice preference, under your usage pattern that does not appear to be a good idea. As long as your disk I/O isn't saturated you want to be running vacuums a lot more often than you are. (Analyze should only be needed if the distrution of values is changing constantly. An example would be timestamps indicating when an update occured.) ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [BUGS] BUG #1698: Different behavior in UNIQUE and DISTINCT
On Thu, Jun 02, 2005 at 20:22:07 +0100, Mauro Delfino <[EMAIL PROTECTED]> wrote: > > I have the these two tables: > CREATE TABLE table_one > ( field1 VARCHAR(255) ); > > CREATE TABLE table_two > ( field1 VARCHAR(255) UNIQUE ); > > table_one has 500k records. I certify that all strings are distinct with > this query: > SELECT DISTINCT field1 FROM table_one; > The query results 500k rows. > > But if I try to insert the records of table_one into table_two with the > following command: > INSERT INTO table_two (field1) (SELECT field1 FROM table_one); > This error occurs: > ERROR: duplicate key violates unique constraint "table_two_field1_key" > > What happened? DISTINC and UNIQUE have different algorithms to determine > when two strings are equal? Are you sure table_two is empty when you do this? ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [BUGS] BUG #1697: Select getting slower on continously updating data
This does not belong on the pgsql-bugs list. The pgsql-novice or pgsql-performance lists seem more appropiate. I have set followups to the pgsql-novice list. On Thu, Jun 02, 2005 at 12:05:00 +0100, Bahadur Singh <[EMAIL PROTECTED]> wrote: > > Hello, > > I found situtation that, when I am selecting data from a table of 200 > records, getting slower as I do continous update to the same existing data. You need to be vacuuming (and possibly analyzing) the table more often as the updates will leave dead rows in the table which will bloat the table size and slow down access, particularly sequential scans. If the updates modify the data value distributions significantly, then you will also need to reanalyze the table to help the planner make good decisions. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [BUGS] BUG #1686: Regular expression problem
On Tue, May 31, 2005 at 11:15:23 -0400, Alvaro Herrera <[EMAIL PROTECTED]> wrote: > > (Note that our ~ regexes are powered by code of Tcl's heritage, so you > could check Tcl's manual on regexes.) Aren't they based directly on Henry Spencer's code? ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [BUGS] Bulletin Board
On Mon, May 23, 2005 at 11:04:36 -0400, "Goar, Saul" <[EMAIL PROTECTED]> wrote: > Do you have a Bulletin Board, or web site for questions and answers. There are FAQs referenced off the Postgresql web site. If you want to ask specific questions than you should use the appropiate mailing lists. The mailing lists are gated to some nonstandard newsgroups which would provide an alternate way of submitting your questions, but my impression is that that just slows things down. ---(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
Re: [BUGS] BUG #1578: ::bit(n) behaves "differently" if applied to bit
On Thu, May 12, 2005 at 10:40:34 -0500, Celia McInnis <[EMAIL PROTECTED]> wrote: > Thanks very much Bruce and Tom for your responses and explanations. The > current mode of operation is mathematically disturbing so I'm hoping that you > can figure out some sort of a fix! Perhaps this example is a little more > convincing than my last one at showing the bug/feature: > > 949 base 10 = 1110110101 base two. > > select 949::bit(10) gives 1110110101 (as expected). Why is that expected? Based on your reasoning for the last case I would expect '00', since the first 10 bits of that integer are 0. > select 949::bit(10)::bit(3) gives 111 (the 3 most significant bits) > select 949::bit(3) gives 101 (the 3 least significant bits). > > As a mathematician, I'd certainly at least want the last two selects to give > the same results! ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [BUGS] BUG #1633: about transactions and row level locking
On Wed, Apr 27, 2005 at 13:56:02 +0100, deepak <[EMAIL PROTECTED]> wrote: > > The following bug has been logged online: This isn't a bug, its a question. I am moving the discussion over to the general list. > Bug reference: 1633 > Logged by: deepak > Email address: [EMAIL PROTECTED] > PostgreSQL version: 8.0.1 > Operating system: MS windows 2000 Server > Description:about transactions and row level locking > Details: > > How we can know a record is locked before updating the record. The purpose > is to avoid deadlock or queuing of update statements while using "select * > from table for update". You might be able to use a short timeout on the statement that tries the update. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [BUGS] BUG #1581: Problem with capitalised DB names...
On Mon, Apr 11, 2005 at 09:37:08 +0100, "Marcus v. Scotti" <[EMAIL PROTECTED]> wrote: > > Thanks... (maybe include this in the manuals, I couldn't find this > information...) It is: http://developer.postgresql.org/docs/postgres/sql-syntax.html#SQL-SYNTAX-IDENTIFIERS ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [BUGS] BUG #1582: boolean DEFAULT TRUE results in FALSE for NULL values
On Tue, Apr 05, 2005 at 21:11:12 +0100, Eric McGough <[EMAIL PROTECTED]> wrote: > > COPY my_table FROM STDIN WITH DELIMITER AS '|'; > \N|\N|\N > \. COPY doesn't provide a way to use default values for just some rows. You can get the default for a column by leaving it out of an explicit column list. But that will apply for all rows. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [BUGS] BUG #1581: Problem with capitalised DB names...
On Tue, Apr 05, 2005 at 18:09:25 +0100,
"Marcus v. Scotti" <[EMAIL PROTECTED]> wrote:
>
> Description:Problem with capitalised DB names...
> Details:
>
> When trying to grant database privileges to a group of users there is no way
> to do this if the database name is capitalised (i.e. db-name = Test). The
> error message when performing the grant operation:
>
> grant all on DATABASE test to group tgroup;
>
> is:
>
> ERROR: database "test" does not exist
>
> I have used capitalisation an a number of databases, so this now seems to
> require me to rename them all in to lower-case...
You need to use double quotes (") around database, table or column names that
are not all lowercase.
---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings
Re: [BUGS] BUG #1567: can't hide password with pg_autovacuum
On Tue, Mar 29, 2005 at 03:54:48 +0100, Olivier Thauvin <[EMAIL PROTECTED]> wrote: > > Maybe I will workaround by setting postgres user access as 'trust' for local > connection only, but I have to reread the doc before :). Ident is better if you can use domain sockets and the equivalent of getpeerid on your OS. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [BUGS] BUG #1483: Index problem
On Tue, Feb 15, 2005 at 12:38:17 +, Zoltan Szmutku <[EMAIL PROTECTED]> wrote: > > My next commands : > > 1. > CREATE UNIQUE INDEX kod > ON proba > USING btree > (mezo1) > > 2. > CREATE UNIQUE INDEX kod > ON proba2 > USING btree > (mezo1) > > The second command not success, I get the error message : > > ERROR: relation "kod" already exists > > Why ? Because you tried to name both indexes the same name. Perhaps you might use something like proba_kod and proba2_kod? ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [BUGS] BUG #1408: don't see index
On Tue, Jan 18, 2005 at 09:01:29 +, andrzej <[EMAIL PROTECTED]> wrote: > > The following bug has been logged online: > > Bug reference: 1408 > Logged by: andrzej > Email address: [EMAIL PROTECTED] > PostgreSQL version: 8.0.0-rc5 > Operating system: windows XP > Description:don't see index > Details: > > Create table t1 > ( > v varchar(5) not null, > m varchar(20), > d date, > l varchar(10), > primary key(v) > ); > > explain select * from t1 where v='abc' > result: > Seq Scan on t1 ( > Filter:((v)::text='abc'::text) > DON'T SEE INDEX PKEY ??? > > but Postgres 7.4.2 Linux > explain select * from t1 where v='abc' > result: > Index Scan using t1_pkey on t1(... > Index Cond((v)::text='abc'::text) > SEE INDEX PKEY OK ! This probably isn't a bug. In some circumstances a sequential scan is faster than an index scan. How many rows are in this table? Have you run an ANALYZE on the table? ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [BUGS] BUG #1388: documentation/announcement suggestion
On Wed, Jan 12, 2005 at 08:31:09 +, Holger Klawitt <[EMAIL PROTECTED]> wrote: > > There is no (at least no apparent) link > to the ChangeLog for the most recent version > of postgres in neither the announcements nor in the > news nor in the download section (at top level). > > I'd think that the ChangeLog is of more interest than the list of supported > platforms - imagine how often someone wants to look at either :-) For most people I expect the release notes are more useful than the change log. You can see the release notes in the development copy of the web documentation (http://developer.postgresql.org/docs/postgres/release.html). ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [BUGS] BUG #1349: Gap in documentation - pg_dump
On Thu, Dec 16, 2004 at 20:21:48 +0100, Piotr Likus <[EMAIL PROTECTED]> wrote: > > Yes, maybe it is insecure, but sometimes "quick and dirty" solutions > are better than no solution at all. > After all you can add "not recommended" note to this tip. The official documentation isn't likely to be changed to instruct people how to incorrectly set up their system. It may be that there is a deficiency in the pg_dump documentation if you are expected to know something from another part of the manual that it may not be obvious that you should know. In that case the answer is to refer to the other section or to include enough information to avoid needing to refer to the other section.` ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [BUGS] BUG #1349: Gap in documentation - pg_dump
On Thu, Dec 16, 2004 at 08:16:56 +, PostgreSQL Bugs List <[EMAIL PROTECTED]> wrote: > > I'm sorry, but I didn't found a better place to report that: > > There is no information how to specify user's password in pg_dump > documentation. I have spent half of day searching for that (pg_hba.conf was > not working for me) and the answer is very simple: > > You can use SET variables for that: > > PGUSER=web-user > PGPASSWORD=secret > > Please include that in pg_dump documentation. You don't want to do that; on many systems the contents of environment variables are visible to other processes. You can keep a password in ~/.pgpass and that will work for more than just pg_dump. If the batch scripts are running on the same machine as the server, consider using ident authentication. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [BUGS] concat fails 'sometimes' on empty colums in select
On Sat, Dec 11, 2004 at 03:33:07 +0100, Stefan de Konink <[EMAIL PROTECTED]> wrote: > Hi, > > I have a table with some empty colums, if I try to concat my colums to > eachother something strange happens. Rather empty column B produces > virtually no output using the || in the select query. But the half filed > colum A does give the desired output. Empty strings and NULL values aren't the same thing. You probably either want to store empty strings or use coalesce to change NULL to an empty string before using concatenation, as a NULL concatenated with anything results in NULL. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [BUGS] BUG #1332: wrong results from age function
On Mon, Nov 29, 2004 at 11:37:50 +,
PostgreSQL Bugs List <[EMAIL PROTECTED]> wrote:
>
> The following bug has been logged online:
>
> Bug reference: 1332
> Logged by: Robert Grabowski
>
> Email address: [EMAIL PROTECTED]
>
> PostgreSQL version: 7.4.6
>
> Operating system: Linux
>
> Description:wrong results from age function
>
> Details:
>
> select age('2004-02-01'::date, '2004-01-01'::date);
> age
> ---
> 1 mon
> (1 row)
>
> select age('2004-03-01'::date, '2004-02-01'::date);
>age
> -
> 29 days
> (1 row)
>
> I think, it should be "1 mon".
I get "1 mon" when I try this:
Welcome to psql 7.4.6, the PostgreSQL interactive terminal.
Type: \copyright for distribution terms
\h for help with SQL commands
\? for help on internal slash commands
\g or terminate with semicolon to execute query
\q to quit
area=> select age('2004-03-01'::date, '2004-02-01'::date);
age
---
1 mon
(1 row)
I am actually using a version post 7.4.6 from cvs. I have also built
postgres using integer date time values.
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [BUGS] [pgsql-bugs] pg_dumpall (7.3) 'public' schema bug
On Wed, Nov 17, 2004 at 11:53:10 -0800, Josh Berkus <[EMAIL PROTECTED]> wrote: > Karl, > > > I don't care that much about the behavior, it's easy enough > > to delete 'public'. I do think that a note should be > > made in the administrator manual regards system upgrades > > where pg_dump(all) scripts are given if this is going to be > > the behavior. > > This isn't isolated to the "public" schema. In fact, anything which is in > the template database (usually template1) will be in the database you reload, > even if it wasn't in the original database. The result is that when you try > to remove built-in objects that ship with PostgreSQL, they are "replaced" on > a new migration server. pg_dump isn't capable of working around this, nor > should it be. > > Search the archives of -Hackers mailing list for this issue; a few > workarounds were suggested. I am pretty sure that the last time this was discussed, it was pointed out that pg_dump(all) and pg_restore are relative to template0, not template1. (Though by default template1 will be the same as template0.) ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [BUGS] BUG #1252: Optimization of SELECT for NOT NULL case
On Wed, Sep 15, 2004 at 23:29:43 +0300, Alexander Kirpa <[EMAIL PROTECTED]> wrote: > On 15 Sep 2004, at 12:21, Bruno Wolff III wrote: > > Regarding your point of view possible exist reason for remove > optimization for case like below > SELECT count(*) from x where id>1 AND id<0 > I basically don't see any significant difference in optimization > for "id IS NULL" and "id>1 AND id<0" People generally know whether or not a table is NOT NULL and aren't very likely to write queries searching for NULL values in such a table. A general constraint handler would be useful (for example in handling partitions of data sets), but just doing it for NOT NULL seems like it would be a net loss. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [BUGS] BUG #1252: Optimization of SELECT for NOT NULL case
On Tue, Sep 14, 2004 at 00:57:07 +0100, PostgreSQL Bugs List <[EMAIL PROTECTED]> wrote: > > Description:Optimization of SELECT for NOT NULL case > > Details: > > CREATE TABLE x (id int4 NOT NULL); > Populate table, for instance, 10M rows; > SELECT count(*) from x WHERE x IS NULL; > Optimizator should rewrite "x IS NULL" to simple "false" Based on responses to other optimization requests I have seen, I think the answer to this one is going to be that it isn't worth paying the cost for every query to check for this case, since no one is going to write a query like this except by mistake. ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [BUGS] \ escapes in check constraint strings?
On Fri, Jul 16, 2004 at 16:49:25 -0500,
Bruno Wolff III <[EMAIL PROTECTED]> wrote:
> I see the following behaivor on 7.4.3 and 7.4 stable from about a week ago:
> bruno=> create table test ();
> CREATE TABLE
> bruno=> alter table test add constraint test2 check('\\' = '');
> ALTER TABLE
> bruno=> \d test
> Table "public.test"
> Column | Type | Modifiers
> +--+---
> Check constraints:
> "test2" CHECK ('\\'::text = ''::text)
>
> bruno=> select '\\';
> ?column?
> --
> \
> (1 row)
>
> I didn't see \\ left as \\ in at least some 7.3 versions.
>
> I think this is a bug, but maybe there are different escape rules for
> strings in check constraints.
>
> In the real case that this derives from I was getting a different number
> of backslashes than I expected in a regular expression.
I think I have figured out what was going on. It looks like [\] in 7.3
regular expressions would try to match a \, but in 7.4 the ] was escaped
resulting in an invalid regular expression. So the real problem was
that in 7.3 I was using something like '[\\]' when I should have been
using '[]'.
While I was trying to figure out what was going on I was confused by
the \d output putting 2 \s where there was only one. Presumably this
is so the output could be used to recreate the constraint. This seems
reasonable, but it this particular case confused me.
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faqs/FAQ.html
[BUGS] \ escapes in check constraint strings?
I see the following behaivor on 7.4.3 and 7.4 stable from about a week ago:
bruno=> create table test ();
CREATE TABLE
bruno=> alter table test add constraint test2 check('\\' = '');
ALTER TABLE
bruno=> \d test
Table "public.test"
Column | Type | Modifiers
+--+---
Check constraints:
"test2" CHECK ('\\'::text = ''::text)
bruno=> select '\\';
?column?
--
\
(1 row)
I didn't see \\ left as \\ in at least some 7.3 versions.
I think this is a bug, but maybe there are different escape rules for
strings in check constraints.
In the real case that this derives from I was getting a different number
of backslashes than I expected in a regular expression.
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faqs/FAQ.html
Re: [BUGS] UNIQUE not honoured for NULL
On Mon, Jul 12, 2004 at 14:47:34 +0200, Gaetano Mendola <[EMAIL PROTECTED]> wrote: > > As someone says NULL = NULL is false As someone else pointed out, NULL = NULL is NULL which is not TRUE. ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [BUGS] Possible bug?
On Fri, Jul 02, 2004 at 10:27:35 -0400, Tom Lane <[EMAIL PROTECTED]> wrote: > > IIRC there were objections the last time it was seriously proposed, > basically of the form "but that will break my application which relies > on writing so-and-so without a cast". We did get as far as removing all > the implicit cross-category coercions except these casts to text: > > bigint | text > smallint| text > integer | text > real| text > double precision| text > numeric | text > oid | text > date| text > time without time zone | text > time with time zone | text > timestamp without time zone | text > timestamp with time zone| text > interval| text > > but these seem to have a constituency :-( I think it is reasoable to expect people to use an explicit cast when doing these conversions. These are not conversions that you would normally want to do. And even if you do, you need to be very concerned about how the converted text looks for different ranges of source data values. Allowing these makes it harder to notice mistakes. And I expect that overall the hiding mistakes case occurs a lot more than the I meant to do that but didn't want to type ::text case. ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [BUGS] BUG #1186: Broken Index?
On Fri, Jul 02, 2004 at 04:50:07 -0300, PostgreSQL Bugs List <[EMAIL PROTECTED]> wrote: > > The following bug has been logged online: This doesn't appear to be a bug at this point. It sounds like you have a self induced performance problem, so I am moving the discussion to pgsql-performance. > > Bug reference: 1186 > Logged by: Gosen, Hitoshi > > Email address: [EMAIL PROTECTED] > > PostgreSQL version: 7.4 > > Operating system: linux 2.4.18 > > Description:Broken Index? > > Details: > > Hello All, > We are using PostgreSQL 7.4.2 for our website that handles over 200,000 > transactions a day. > About a month ago, the responses from the SELECT queries on the database > became terribly slow. > We tried to anaylze the cause of the problem, searching throught the system > logs and all, but nothing appeared to be out of the ordinary. > > What we did to resolve this was to dump the database, delete the database, > recreate the database, and finally restore it. After that, things were back > to normal. > > From the above experience, we were able to hypothesize that the fault of the > slow responses was not from a broken data or hardware failures, but from a > broken index, since we were able to recover 100% of the data on the same > machine. > > Today, the same problem occured, and the same actions are going to be taken > to temporary resolve it. > > Final note: we will also experiment with the 'vacuum full' command to see > if it counters this problem. It sounds like you aren't properly vacuuming your database. It is possible that you need a higher FSM setting or to vacuum more frequently. ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [BUGS] string concatenator || and NULL values
On Thu, Jul 01, 2004 at 16:42:29 -0300, Roberto João Lopes Garcia <[EMAIL PROTECTED]> wrote: > > When I use string concatenator || and there are fields of type date whith NULL values > the result is an enpty string. Please see example: This isn't a bug. If either operand of || is NULL, then the result is NULL. You probably want to use the coalesce function to change NULLs to empty strings. ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [BUGS] BUG #1176: PHP connection
On Tue, Jun 22, 2004 at 06:29:29 -0300, PostgreSQL Bugs List <[EMAIL PROTECTED]> wrote: > > The following bug has been logged online: This isn't really a bug; you should have posted you question to pgsql-novice. (Followups set to there and to you.) > > Bug reference: 1176 > Logged by: thoyyib > > Email address: [EMAIL PROTECTED] > > PostgreSQL version: 7.4 > > Operating system: linux > > Description:PHP connection > > Details: > > The error report is : > FATEL: IDENT authentication failed for user "postgres" > I am a new user. pls help me The simplest solution is probably to su to root and then su to postgres and then do whatever you were trying to do. > > > ---(end of broadcast)--- > TIP 7: don't forget to increase your free space map settings ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [BUGS] inserting lots of values into a column that has "unique" property
On Mon, Jun 14, 2004 at 01:07:09 +0800, Fehmi Noyan ISI <[EMAIL PROTECTED]> wrote: > > I got the error message as expected. Everything is ok.Now, let's create > another table called "table_2" with an inheritence from "table_1". > > But, when I insert a new value into "table_2" with an "id" value same > as with one of the values in "table_1"... > > I think, I must get an error message like the message above (when I try > to insert a new value directly into "table_1" with a same "id" value). > There are two same "id"s with the value "1" although the "id" column is > defined as UNIQUE. > If I use "PRIMARY KEY" instead of "UNIQUE NOT NULL", the same thing > happens.Nothing changes! This is a deficiency in how inherited tables work. This will not be fixed in the upcoming 7.5 release. If you need this, you may be better off using views (of joined tables) rather than inherited tables. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [BUGS] Question regarding 'not in' and subselects
On Wed, May 26, 2004 at 13:57:37 -0400,
Vic Ricker <[EMAIL PROTECTED]> wrote:
> Hi. I'm using PostgreSQL 7.4.2 under Fedora Core 1.
>
> I have two tables with a single varchar(32) column in each. I'm trying
> to find all the rows from one table that don't exist in the other
> table. The query that I am using is:
>
> select u.user_name from users u where u.user_name not in (select
> user_name from iasusers);
>
> (Actually, I'm doing something a bit more complex but this illustrates
> the problem.)
>
> It always seems to return 0 rows. As a test, I inserted a row into
> users that I knew wasn't in iasuses but it didn't make a difference.
>
> If I remove the 'not', the query returns the rows that exist in both
> tables.
>
> If I replace the subselect with a list, it seems to work the way that
> I'd expect, i.e. "not in ('vic', 'joe')" it shows all the rows from
> users except for vic and joe.
>
> Am I doing something wrong or is this a bug?
Are there any nulls in iasusers.user_name?
---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings
Re: [BUGS] BUG #1139: Auto-increment and Rollback
On Mon, Apr 19, 2004 at 20:16:15 -0300, PostgreSQL Bugs List <[EMAIL PROTECTED]> wrote: > > The following bug has been logged online: > > Bug reference: 1139 > Logged by: Emil Tarazi > > Email address: [EMAIL PROTECTED] > > PostgreSQL version: 7.4 > > Operating system: Linux > > Description:Auto-increment and Rollback > > Details: > > If a query is called within a BEGIN-COMMIT/ROLLBACK block that > auto-increments some field, a ROLLBACK will not decrement that field. Assuming you are refering to sequences, that's the way it is supposed to work. You really should only be using sequences to get unique values. Within a single session expecting the values to be monotonically increasing is also valid. Other than that you shouldn't be making any assumptions about the relation between returned values. ---(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
Re: [BUGS] Sequential Scan Index Bug
On Sat, Apr 03, 2004 at 13:51:56 -0500, Gabriel Weinberg <[EMAIL PROTECTED]> wrote: > > I have a table with an integer column with about 10M rows in it. > > This column has an index (btree). > > When I try to select a row using this column with an integer, e.g. select * > from table where id=4, it always uses the index. However, if I select try > to select a row using this column with a decimal, e.g. select * from table > where id=4.343, it skips the index entirely and does a sequential scan of > the table. > > I am using v7.4.2 on Freebsd 4.9. Depending on what you want to do, you probably either want to cast the value to an int explicitly or combine that with a test (using a stable function) to make sure the number is actually an integer. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [BUGS] Bug#225680: Fwd: Default pg_autovacuum config glitches
On Sat, Mar 27, 2004 at 00:56:47 +0100, Martin Pitt <[EMAIL PROTECTED]> wrote: > > Well, daemons that run for a long time should not log to > stdout/stderr, but to syslog. Otherwise they clutter up the screen > randomly or the messages are not seen at all (when you are only > working under X for example). pg_autovacuum already does that > (/var/log/postgresql/autovacuum_log in Debian), so I don't see any > reason why it should not log errors there, too. Some people don't want to use syslog for logging for various reasons. Logging to stdout is very attractive because your can pipe the output to your favorite logging program. > > If you don't want that ability you can redirect the stdin, stdout and > > stderr yourself. > > I already thought about that, but if there's a startup problem with > autovacumm then I would not see this as well, but I want that. An > ideal daemon attempts to start and either writes an error to stderr > why it cannot start, or detaches from its terminal when it started > successfully. I think you will find that there are a number people that will disaggree with you about this. Why would the program write some errors only to stdout (or stderr) and others to syslog? If the program were to always write errors to syslog (and maybe copy them to stderr) you would find the error messages in the logs even if the program was not able to fully start up. > If pg_autovacuum logged everything to syslog, then immediate > redirection would be possible (but not my favourite solution). I am not sure what kind of logging the autovacuum deamon currently does. I currently pipe the output from the postmaster to multilog to do my logging and don't use syslog. I am not currently using the autovacuum daemon so do not have examples of what it logs. However, I probably will use it in the future and don't want it to be only usable with syslog. ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [BUGS] Fwd: Default pg_autovacuum config glitches
On Thu, Mar 25, 2004 at 16:08:49 +0100,
Martin Pitt <[EMAIL PROTECTED]> wrote:
> Hi PostgreSQL hackers!
>
> A while ago we received the bug report below against pg_autovacuum.
> Since it runs as a daemon, it should detach from its controlling
> terminal by executing sth like
>
> int nullfd = open("/dev/null", O_RDWR);
> dup2(nullfd, 0);
> dup2(nullfd, 1);
> dup2(nullfd, 2);
> if (nullfd != 0 && nullfd != 1 && nullfd != 2)
> close(nullfd);
>
> (taken from syslog-ng which does it properly).
That doesn't look like a good idea. This will prevent any logging to
stdout or stderr.
If you don't want that ability you can redirect the stdin, stdout and
stderr yourself.
>
> Can you do that in the next release?
>
> Thanks in advance and have a nice day!
>
> Martin
>
> - Forwarded message from [EMAIL PROTECTED] -
>
> Date: 31 Dec 2003 15:02:22 -
> From: [EMAIL PROTECTED]
> To: [EMAIL PROTECTED]
> Subject: Default pg_autovacuum config glitches
> X-Spam-Status: No, hits=-3.8 required=4.0 tests=HAS_PACKAGE,NO_REAL_NAME
> autolearn=no
> version=2.60-master.debian.org_2003_11_25-bugs.debian.org_2003_12_29
>
> Package: postgresql
> Version: 7.4.1-1
>
> When /etc/init.d/postgresql is run, pg_autovacuum maintains connections
> to the startup tty on fds 0, 1 and 2. When run from the console (as part
> of (apt-get upgrade), this caused some hiccups to a following getty.
>
> In any case, it's considered bad behaviour on the part of a long-running
> daemon.
>
> (If you feel this is pg_autovacuum's fault rather than the init script's,
> please reassign to postgresql-contrib.)
>
> Thanks!
>
> - End forwarded message -
>
> --
> Martin Pitt Debian GNU/Linux Developer
> [EMAIL PROTECTED] [EMAIL PROTECTED]
> http://www.piware.de http://www.debian.org
---(end of broadcast)---
TIP 8: explain analyze is your friend
Re: [BUGS] Fwd: Infinite recursion in function causes DoS
On Thu, Mar 25, 2004 at 12:25:33 +0100, Martin Pitt <[EMAIL PROTECTED]> wrote: > > Either way, this situation leads to a DoS of the database system or the > entire machine. Since any user with enough privileges to access the > database can create and execute functions, this raises a slight security > concern. It is going to be very difficult to prevent authorized postgres users from DOSing the server. While fixing this issue will make it harder to accidentally shoot ones self in the foot, you cannot expect to give untrusted users access to postgres and have the server be secure from DOS attacks. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [BUGS] BUG #1094: date_part('week') bug
On Thu, Mar 04, 2004 at 13:56:42 -0400,
PostgreSQL Bugs List <[EMAIL PROTECTED]> wrote:
>
> The following bug has been logged online:
This isn't a bug. Weeks that span years are treated as part of the year
in which Thursday of that week lies.
>
> Bug reference: 1094
> Logged by: xterm
>
> Email address: [EMAIL PROTECTED]
>
> PostgreSQL version: 7.3.4
>
> Operating system: RH Fedora
>
> Description:date_part('week') bug
>
> Details:
>
> Some late dates give in some years wrong
> date_part('week')
>
> examples:
>
> select date_part('week', '2003-12-30'::date);
> date_part
> ---
> 1
> (1 row)
>
> select date_part('week', '1997-12-29'::date);
> date_part
> ---
> 1
> (1 row)
>
> but:
>
> select date_part('week', '1998-12-31'::date);
> date_part
> ---
> 53
> (1 row)
>
>
>
>
> ---(end of broadcast)---
> TIP 4: Don't 'kill -9' the postmaster
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
Re: [BUGS] [HACKERS] CHECK constraints inconsistencies
On Mon, Mar 01, 2004 at 20:28:02 -0500, Tom Lane <[EMAIL PROTECTED]> wrote: > Michael Glaesemann <[EMAIL PROTECTED]> writes: > > In both cases, the CHECK constraint uses a function that is stable or > > volatile. It was suggested that functions used in CHECK constraints be > > restricted to immutable, > > This seems reasonable to me. I'm a bit surprised we do not have such a > check already. There may be times you want to do this. For example you may want a timestamp to be in the past. In this case as long as it was in the past when the data was entered it will continue to be in the past (barring someone resetting the system time). This is something someone might actually check unlike comparing to random numbers. I think just noting that check constraints are only checked on inserts and updates and that this means that check constraints using volatile or stable functions need to be well thought out. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [BUGS] BUG #1083: Insert query reordering interacts badly with
I am going to try to move this over the sql list, since it doesn't belong
on bugs.
On Tue, Feb 24, 2004 at 23:47:48 +1300,
Martin Langhoff <[EMAIL PROTECTED]> wrote:
> Tom Lane wrote:
>
> >How about
> >
> >SELECT nextval('seq'); -- ignore result
> >
> >INSERT INTO ... VALUES (currval('seq'), currval('seq'));
> >
> >
>
> Well, it works for my sample case, I have to agree. Maybe I should
> mention that I tried to boil down the bugreport to the simplest repro
> case I could.
>
> My actual SQL looks roughly like
>
>INSERT INTO destination (record_id, page, row)
>SELECT
>(SELECT record_id FROM record ORDERED BY name),
>(NEXTVAL('seq') / 200),
>(CURRVAL('seq') % 200)
>
> While I have a workaround, I am definitely curious as to whether there
> is actually a way to do it. Thanks for your patience.
I think the following will do what you want:
INSERT INTO destination (record_id, page, row)
SELECT record_id, seq/200, seq%200 FROM
(SELECT record_id, nextval('seq') as seq FROM record ORDERED BY name);
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [BUGS] BUG #1083: Insert query reordering interacts badly with NEXTVAL()/CURRVAL()
On Mon, Feb 23, 2004 at 21:26:57 -0400,
PostgreSQL Bugs List <[EMAIL PROTECTED]> wrote:
>
> Details:
>
> === SQL ===
>
> CREATE TEMP TABLE testing (col_a integer, col_b integer);
> CREATE TEMP SEQUENCE seq;
>
> /* this statement will produce the expected result */
> INSERT INTO testing (col_a, col_b) VALUES (NEXTVAL('seq'), CURRVAL('seq'));
>
> /* this statement will reverse the order of CURRVAL()/NEXTVAL() to match the
> column order of the table */
> INSERT INTO testing (col_b, col_a) VALUES (NEXTVAL('seq'), CURRVAL('seq'));
I don't think an order of evaluation is guarenteed for INSERT VALUE lists.
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [BUGS] date_part returns different results with same interval.
On Thu, Feb 19, 2004 at 16:52:37 -0800, Wade Klaver <[EMAIL PROTECTED]> wrote: > Hello folks. > > I just noticed some funky behaviour on the part of date_part. If there is > some reason this is correct behaviour, I wouldn't mind knowing why. > The problem is that date_part can return different results given to > essentially identical intervals. It seems to maybe be obeying the letter of > the law if not the spirit? The following session from a -CURRENT build > demonstrates this. Intervals have two parts. One is an absolute time difference (I think stored in seconds), that should be used for getting days, hours, minutes and seconds. The other part is a difference in months that is used for getting months and years. Under some circumstances months get converted to 30 days each. It seems reasonable that date_part keeps these parts separate as it allows a way to look at each part of the interval. I don't know if there is another function that allows you to do that. > Thanks in advance. > > wade=# select age(now(), 'Jan 1, 2002'::date); > age > --- > 2 years 1 mon 18 days 16:24:54.4191970001 > (1 row) > > wade=# select date_part( 'years', age(now(), 'Jan 1, 2002'::date)); > date_part > --- > 2 > (1 row) > > wade=# select now() - 'Jan 1, 2002'::date; >?column? > -- > 779 days 16:25:03.9250539988 > (1 row) > > wade=# select date_part( 'years', now() - 'Jan 1, 2002'::date); > date_part > --- > 0 > (1 row) > > > -- > Wade Klaver > Wavefire Technologies Corporation > GPG Public Key at http://archeron.wavefire.com > > /"\ ASCII Ribbon Campaign . > \ / - NO HTML/RTF in e-mail . > X - NO Word docs in e-mail . > / \ - > > > ---(end of broadcast)--- > TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED]) ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [BUGS] Problem with datatype REAL using the = (EQUAL) operator
On Thu, Feb 12, 2004 at 12:46:27 -0600, Javier Carlos <[EMAIL PROTECTED]> wrote: > > bd_temporal=> SELECT * FROM tbl_temp WHERE var < 0.20; > var > -- > 0.1 > 0.11 > (2 rows) > bd_temporal=> SELECT * FROM tbl_temp WHERE var = 0.11; > var > - > (0 rows) > bd_temporal=> SELECT * FROM tbl_temp WHERE var = '0.11'; > var > -- > 0.11 > (1 row) > > > If you know how this problem might be fixed, list the solution below: If you want exact fractional numbers you should be using numeric, not float. The problems you are seeing has to do with single precision and double precision versions of .11 not being equal. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [BUGS] (none)
On Tue, Dec 30, 2003 at 19:32:03 +0530, Sh A Guru Prasad <[EMAIL PROTECTED]> wrote: > > Hi, How to convert number format (with decimal) into > integer only ex:to conver 100.00 to 100. Depending on what kind of rounding you want, you can just cast to an integer. You could also use ceil, floor or round. > One more > Do we have any code to convert number to words > ex: 1234 to one thousand two hundredn and thirty four. I don't think there is currently a supplied function to do this. You will need to write your own. > (I want to use for indian currency only, i will use crores > instead of billions) > > Thank you > > > > ---(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 ---(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
Re: [BUGS] BUG #1001: Inconsistent authentication between psql and PQconnectdb - possible security implications?
On Fri, Dec 05, 2003 at 12:47:58 -0400, PostgreSQL Bugs List <[EMAIL PROTECTED]> wrote: > > All is well with psql authentication. However, when I tried to > use knoda/hk_classes to access the database, I could not get authenticated. A > typical error message was IDENT authentication failed for user "irwin". When I > traced this down through the hk_classes code it was using PQconnectdb to connnect to > the database, and there were complaints in the postgresql log that the identd server > was not available. All knoda/hk_classes/PQconnectdb problems disappeared when I > installed identd (apt-get install pidentd) on my Debian stable system. So all seems > well when identd is installed, but there may be a security concern with psql when it > is not. On the other hand, if psql is actually secure when identd is not running, > then why isn't PQconnectdb using the exact same (secure) method of authentication > for this case? When connecting using domain sockets the local equivalent of getpeeruid is used to determine which user is connecting rather than by asking an ident server. When you use a network socket, then the user is checked by asking the ident server at the same IP address as the connection is coming from. ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [BUGS] before trigger doesn't, on insert of too long data
On Mon, Nov 10, 2003 at 21:48:30 +0100, Gaetano Mendola <[EMAIL PROTECTED]> wrote: > Seems to me too, from the standard: > > "The order of execution of a set of triggers is ascending by value of > their timestamp of creation in their > descriptors, such that the oldest trigger executes first. If one or more > triggers have the same timestamp value, > then their relative order of execution is implementation-defined." I don't think Postgres uses that ordering. My memory is that it is based on the collation order of the trigger name because that allowed better control of trigger firing order. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [BUGS] is this my date problem
On Wed, Oct 01, 2003 at 10:26:57 -0700,
Theodore Petrosky <[EMAIL PROTECTED]> wrote:
> I am not sure but i thought I read something of this
> problem and maybe its not a problem but my
> understanding.
>
> There are only 30 days in september however I can not
> seem to make a query that asks for october 1.
You shouldn't be refering to September 31 as that isn't a valid date.
>From your output it looks like it may have been treated as the same
as October 1, but I wouldn't count on that working in the future.
>
> proofduedate is time stamp with zone
> maybe this is the problem but I am a little lost.
>
>
> SELECT jobnumseq, proofduedate FROM jobs WHERE
> proofduedate BETWEEN to_timestamp('29 September 2003',
> 'DD Month ') AND to_timestamp('31 September 2003
> 23:59', 'DD Month HH24:MI');
> jobnumseq | proofduedate
> ---+
> 10080 | 2003-09-30 17:00:00-04
> 10081 | 2003-09-30 22:00:00-04
> 10082 | 2003-10-01 16:00:00-04
> 10074 | 2003-09-29 16:00:00-04
> 10077 | 2003-09-29 16:00:00-04
> 10078 | 2003-09-29 18:00:00-04
> 10079 | 2003-09-30 15:00:00-04
> 10083 | 2003-10-01 13:00:00-04
> 10084 | 2003-10-01 13:00:00-04
> 10085 | 2003-10-01 13:00:00-04
> 10086 | 2003-10-01 16:00:00-04
> 10087 | 2003-10-01 16:00:00-04
> 10088 | 2003-10-01 16:00:00-04
> (13 rows)
>
>
> Ted
>
> __
> Do you Yahoo!?
> The New Yahoo! Shopping - with improved product search
> http://shopping.yahoo.com
>
> ---(end of broadcast)---
> TIP 7: don't forget to increase your free space map settings
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
Re: [BUGS] Is it bug???
On Wed, Sep 03, 2003 at 23:19:24 -0700, Ringo <[EMAIL PROTECTED]> wrote: > What a prick.. >From observing Tom's responses here, I think this is a real question (not an RTFM response). If the documentation doesn't make it clear to new readers that explain analyze does execute the query (though doesn't return the results), then the documentation needs to get fixed. > > "Tom Lane" <[EMAIL PROTECTED]> wrote in message > news:[EMAIL PROTECTED] > > Dinar <[EMAIL PROTECTED]> writes: > > > I thought that explain analyze doesn't execute query > > > > You thought wrong. Is the manual's explanation not clear enough? > > > > regards, tom lane > > > > ---(end of broadcast)--- > > TIP 9: the planner will ignore your desire to choose an index scan if your > > joining column's datatypes do not match > > > > > > > ---(end of broadcast)--- > TIP 4: Don't 'kill -9' the postmaster ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [BUGS] [PERFORM] pgsql inserts problem
On Wed, Aug 27, 2003 at 15:50:32 +0300, Tarhon-Onu Victor <[EMAIL PROTECTED]> wrote: > > The problems is that only ~15% of the lines are inserted into > the database. The same script modified to insert the same data in a > similar table created in a MySQL database inserts 100%. Did you check the error status for the records that weren't entered? My first guess is that you have some bad data you are trying to insert. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [BUGS] ALTER SCHEMA problem
On Tue, Aug 19, 2003 at 21:24:50 +0800,
Chris M <[EMAIL PROTECTED]> wrote:
> I also find something magic when using sequence.
> select nextval('seq_test');
> and
> select nextval('"seq_test"');
> both refer to the same sequence: seq_test.
>
> If I want to use a sequence with name: SEQ_TEST,
> I have to write it as:
> select nextval('"SEQ_TEST"');
>
> So single quotes '...' here not like those in WHERE clause.
That depends on your point of view. As far as what gets passed to the nextval
function 's work just like they do in the where clause. However the value
gets treated like the strings used to represent identifiers in SQL where
"s are used to quote identifier names.
> And I think ORACLE's syntax is better.
At some point someone will probably implement the Oracle syntax.
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faqs/FAQ.html
Re: [BUGS] Indexes not used for "min()"
On Tue, Jul 29, 2003 at 09:48:37 -0600, "Valsecchi, Patrick" <[EMAIL PROTECTED]> wrote: > > When doing the following query, it's obvious that postgres should use > the index: > > stats=# explain select min(time) from call; This has been discussed on the mailing lists numerous times. If you are interested in details you can search the archives. If you just want a faster equivalent query use: select time from call order by time limit 1; ---(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
Re: [BUGS] Index speeds up one row table (why)?
On Sun, Jun 01, 2003 at 01:20:03 -0600, Dave E Martin XXIII <[EMAIL PROTECTED]> wrote: > Rod Taylor wrote: > > >An 8 k page will hold approx 140 tuples based on your structure. So, > >for every ~100 updates you'll want to run vacuum (regular, not full) on > >the table > > Alas, for this application, that means a vacuum once every 5 seconds or > so. I'll see if I can set up a separate little task to do that (I assume > at this rate, its better to just keep a connection open, than > setup/teardown). I don't suppose there is a way to get a trigger to do a > vacuum (which doesn't want to be in a transaction) (thinking it could > check for id mod 100=0 or something)? I also assume a few pages isn't > going to be that bad (just don't let it get to 11000 8). Maybe you should reconsider how badly you want the app to be totally database agnostic? Using a sequence might be less of a contortion than using vacuum a few times a minute. You are likely to have similar performance issues with other databases, so this section of code may not turn out to be very portable in any case. ---(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
Re: [BUGS] Bug or not...
On Sun, Jun 01, 2003 at 11:10:56 +0400, Nick Altmann <[EMAIL PROTECTED]> wrote: > Ok. I agree about psql. But how can I do pg_dump??? Please keep discussions on the list. Ident authentication will work for pg_dump if it is supported by your OS. I suspect that .pgpass will work with pg_dump (with 7.3.x), but don't know for sure. It should be easy for you to try this yourself. > > - Original Message ----- > From: "Bruno Wolff III" <[EMAIL PROTECTED]> > To: "Nick Altmann" <[EMAIL PROTECTED]> > Cc: <[EMAIL PROTECTED]> > Sent: Sunday, June 01, 2003 9:23 AM > Subject: Re: Bug or not... > > > > On Fri, May 30, 2003 at 16:33:55 +0400, > > Nick Altmann <[EMAIL PROTECTED]> wrote: > > > Hello! > > > > > > I think it's not bug, but difficulty. > > > > > > I cannot use trusted connections for my localhost. I use password. But > psql, pg_dump, pg_restore doesn't support password parameter in command > line. Also, i cannot do nightly backup if I don't use trusted connections. > Cannot you add password command line parameter in psql and others? > > > > You can put passwords in a file. Putting them on the command line is a bad > > idea anyway as the command line is visible to other users on many systems. > > However you are probably better off using ident authentication. > > > ---(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
Re: [BUGS] Index speeds up one row table (why)?
On Sat, May 31, 2003 at 17:17:38 -0600, Dave E Martin XXIII <[EMAIL PROTECTED]> wrote: > Speaking of which, since the row is locked with select for update (so it > can't be involved in any other transactions anyway) and the change > doesn't change the length of the row, can't it just be updated in-place, > or would that break something else? (pardon if this is answered already, > me thinks its time to go reread the todo's and the architecture > documents...) No. Select for update only blocks writers, not readers. This has important performance advantages. You might want read the documentation on MVCC. Tom Lane also has a copy of a presentation he made on the web somewhere. I have it read it but it has gotten favorable mention on the lists before. ---(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
Re: [BUGS] Index speeds up one row table (why)?
On Sat, May 31, 2003 at 16:56:56 -0600, Dave E Martin XXIII <[EMAIL PROTECTED]> wrote: > > (ok, experimented a bit more just now) > Hm, it appears that degredation occurs with the index as well, I guess > at the time I created the index, it just initially did better because it > got to skip all the already dead rows at creation time: but this is > disturbing, I do a vacuum, and the access times are better, but still > horrible: You really don't want to use an index, so this probably doesn't matter for the current application. The problem is that when data is inserted into an index that just increases (or decreases) in value space from deleted entries doesn't get reused. I believe this is fixed in 7.4. This case would apply to indexes based on counters, dates or times where new values are added and old values get deleted. ---(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
Re: [BUGS] Bug or not...
On Fri, May 30, 2003 at 16:33:55 +0400, Nick Altmann <[EMAIL PROTECTED]> wrote: > Hello! > > I think it's not bug, but difficulty. > > I cannot use trusted connections for my localhost. I use password. But psql, > pg_dump, pg_restore doesn't support password parameter in command line. Also, i > cannot do nightly backup if I don't use trusted connections. Cannot you add password > command line parameter in psql and others? You can put passwords in a file. Putting them on the command line is a bad idea anyway as the command line is visible to other users on many systems. However you are probably better off using ident authentication. ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [BUGS] Index speeds up one row table (why)?
On Sat, May 31, 2003 at 00:14:18 -0600, Dave E Martin XXIII <[EMAIL PROTECTED]> wrote: > version: 7.3.2 > > Currently this table has one row in it, where name is 15 unicode > characters long. It would seem that there would be no need for an index > on name. However, doing: It probably has one visible row in it. If it can changed a lot, there may be lots of deleted tuples in a row. That would explain why an index scan speeds things up. ---(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
Re: [BUGS] Security problem with Postgres sql
On Fri, Mar 21, 2003 at 16:44:07 +0530, DEEPANSHU GARG <[EMAIL PROTECTED]> wrote: > Hi, > We have a problem when logging in postgresql. > We are setting the user id and password using Create User command. > Now when we login the postgresql using > $psql -U username databasename > it does'nt prompt for the password but connects to the database. > The password is correctly set and we examined the pg_shadow table also > where it has been stored in the encrypted format. Please help us with > this. > > Also when we log in the Psql with the small -u option it shows the option > is deprecated , use -U option , prompts for the user id and passsword but > never validates the password. > > If someone else has faced this problem please help us out. This is very > ugently required. Can you show us pg_hba.conf? Most likely you are using trust as the authentication method. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[BUGS] Important typo fix for hba.c in 7.4
A typo in src/backend/libpq/hba.c breaks local ident authentication in the SO_PEERCRED case. elif is misspelled as elsif for the test. A patch is attached. *** hba.c Sat Mar 15 10:15:46 2003 --- hba.c.old Sat Mar 15 10:15:29 2003 *** *** 1248,1254 return true; ! #elif defined(SO_PEERCRED) /* Linux style: use getsockopt(SO_PEERCRED) */ struct ucred peercred; ACCEPT_TYPE_ARG3 so_len = sizeof(peercred); --- 1248,1254 return true; ! #elsif defined(SO_PEERCRED) /* Linux style: use getsockopt(SO_PEERCRED) */ struct ucred peercred; ACCEPT_TYPE_ARG3 so_len = sizeof(peercred); ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [BUGS] performance for MIN,MAX aggregates
On Tue, Mar 11, 2003 at 15:42:41 +0700, Ruslan A Dautkhanov <[EMAIL PROTECTED]> wrote: > Hello all, > > Just a little simple example: > > isbs=# \d radauth >Table "public.radauth" > Column |Type | Modifiers > --+-+--- > dttm | timestamp(0) with time zone | > username | text| > realm| text| > logline | text| > Indexes: radauth_dttm_username btree (dttm, username) > > isbs=# explain select min(dttm) from radauth; > QUERY PLAN > > Aggregate (cost=2591.75..2591.75 rows=1 width=8) >-> Seq Scan on radauth (cost=0.00..2363.00 rows=91500 width=8) > (2 rows) This is discussed a lot in the archives. The short answer is use the following query instead: select dttm from radauth order by dttm limit 1; ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [BUGS] cvs (7/2/2003) broken?
On Tue, Feb 11, 2003 at 00:13:44 +0200, Elias Athanasopoulos <[EMAIL PROTECTED]> wrote: > > And my GNU bison is version 1.28. > > I'll maybe have to upgrade. Thanx for your answer. I think you need to upgrade to at least 1.75. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [BUGS] year and weeknumbers (proposal included)
On Mon, Jan 27, 2003 at 21:53:27 +0100, [EMAIL PROTECTED] wrote: > hi, > > last year (2002) the date december 31st is in week 1 of 2003. > > it is not possible to get this out of a date-column using EXTRACT or > to_char. > to_char( '2002-12-31', 'WW' ) returns 1, and > to_char( '2002-12-31', '' ) returns 2002 (as expected)! It is POSSIBLE to do. You can use the fact that the Thursday of a week is always in the same year as the week number refers to, to get the correct year. It isn't pretty though and it probably is a good idea to have a simple way to get the year corresponding to an ISO week. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [BUGS] COPY problem with bad dates
On Wed, Dec 18, 2002 at 09:14:18 -0600, Bruno Wolff III <[EMAIL PROTECTED]> wrote: > It looks like when a bad date is entered using COPY the backend connection > gets killed. I just tried it with 7.3.1 and get the same error. ---(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
[BUGS] COPY problem with bad dates
It looks like when a bad date is entered using COPY the backend connection gets killed. I noticed this when having a problem loading a database with several copies in one transaction. The backend died consistantly at the same point, but not near where the bad dates were. I eventually found the bad dates and corrected the problem with the load. However I noticed that the error message I got indicated the backend died, so I thought I should try to get a simpler case that shows the same problem. Below is a short psql example run against a 7.3 database that also gets the backend to crash, though not in the middle of a large transaction. bruno=> create table test (col1 timestamp); CREATE TABLE bruno=> copy test from stdin; Enter data to be copied followed by a newline. End with a backslash and a period on a line by itself. >> 11/31/02 >> \. ERROR: copy: line 1, Bad timestamp external representation '11/31/02' lost synchronization with server, resetting connection ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [BUGS] OSX 10.2.2 and beta 5
On Sat, Nov 16, 2002 at 15:18:58 -0500, Tom Lane <[EMAIL PROTECTED]> wrote: > Neil Conway <[EMAIL PROTECTED]> writes: > > Bruce Momjian <[EMAIL PROTECTED]> writes: > >> "Random" randomly fails. It is OK. > > > So why is it a regression test, then? > > It's hard to see how you could test random() in a completely > deterministic fashion ... You could use the floor function and check that function call works and that returned values are in the range >= 0 and < 1 (the description just says between but this is the normal range). You could also do a couple of selects that compare random's output to each other to see if it is generating a constant. The odds of this happening by chance are very small. If you tie a couple of these together you should be able to reduce the chances to whatever you think is safe. The odds of the following returning true should be less than the chances of a hardware hiccup: select random() = random() and random() = random(); The following should always return 0 (assuming I am right about random being strictly less than 1): select floor(random()); You can probably come up with other tricks. For example for a large sample you should be able to put a bound on the average for which the probability of the average being outside that bound is comparable to the test for random returning constant values. The variance is something else that could be tested this way. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [BUGS] Bug #801: now() in transaction
On Fri, Oct 18, 2002 at 07:23:29 -0400, [EMAIL PROTECTED] wrote: > Bhuvan A ([EMAIL PROTECTED]) reports a bug with a severity of 2 > The lower the number the more severe it is. > > Short Description > now() in transaction > > Long Description > I am using 7.2.1. now() returns same value ever, within transaction. If we use now() >say 10 times in the same transaction, all 10 times we are getting the same value, >irrespect of the interval. 7.2 too behaves similar. This is how it is supposed to work. If you need the current time, rather than transaction start time, use timeofday(). This is covered in the documentation. ---(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
[BUGS] atan2 documentation bugs for 7.3 and 7.2
In the section on mathematical functions the following is given for atan2: atan2(x, y) inverse tangent of a/x I believe the correct version should either be: atan2(y, x) inverse tangent of y/x or atan2(x, y) inverse tangent of x/y I beleive the documentation was also wrong for 7.2 where it was: atan2(x, y) inverse tangent of y/x The postgres functions seems to be the same as glibc function of the same name when has the following man information: double atan2(double y, double x); DESCRIPTION The atan2() function calculates the arc tangent of the two variables x and y. It is similar to calculating the arc tangent of y / x, except that the signs of both arguments are used to determine the quadrant of the result. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [BUGS] c.h needed in Pg.c
On Mon, Aug 26, 2002 at 00:34:00 -0400, Tom Lane <[EMAIL PROTECTED]> wrote: > Bruno Wolff III <[EMAIL PROTECTED]> writes: > > When trying to build from CVS, interfaces/perl5 doesn't build unless I add > > an include of "c.h" in Pg.c. > > Works fine here --- and I'm not inclined to include a backend include > file into a frontend module if it can possibly be avoided. Please be > more specific about what failure you're seeing, and on what platform. > > regards, tom lane I sent make output and a config log here (and maybe copied Tom Lane) for this issue. But I think I might have hit a size limit with the message as it hasn't shown up yet. Is there someplace else I should send the information? ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[BUGS] Potential buffer overflow in contrib/cube/cubeparse.y
In the function cube_yyerror an output buffer of 256 bytes is allocated to be used for an error message that outputs some of the input buffer. I think the portion of the input buffer that is echod can potentially be longer than this. ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[BUGS] cube bug accepting -.1
The lex scanner for the cube package has an apparent error in its
pattern for matching floating point numbers. It doesn't except cases
where there is no digit between a sign and a decimal point. For example
-.1 is not matched by the pattern.
I have attached a patch to cubeparse.l and the regression differences.
P.S. I still plan to submit enhancement patches for cube before the
end of the week, but as this is a bug fix I thought it should really
go in separately.
*** cubescan.l Tue Aug 27 09:04:16 2002
--- ../cube/cubescan.l Tue Jul 30 11:33:08 2002
***
*** 34,40
n[0-9]+
integer [+-]?{n}
! real [+-]?({n}\.{n}?|\.{n})
float({integer}|{real})([eE]{integer})?
%%
--- 34,40
n[0-9]+
integer [+-]?{n}
! real [+-]?({n}\.{n}?)|(\.{n})
float({integer}|{real})([eE]{integer})?
%%
*** ./expected/cube.out Sun Aug 25 12:48:54 2002
--- ./results/cube.out Tue Aug 27 09:04:35 2002
***
*** 43,49
(1 row)
SELECT '-.1'::cube AS cube;
! ERROR: parse error, expecting `FLOAT' or `O_PAREN' or `O_BRACKET' at or before
position 2, character ('.', \056), input: '-.1'
SELECT '1.0'::cube AS cube;
cube
--- 43,52
(1 row)
SELECT '-.1'::cube AS cube;
! cube
!
! (-0.1)
! (1 row)
SELECT '1.0'::cube AS cube;
cube
==
---(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
[BUGS] c.h needed in Pg.c
When trying to build from CVS, interfaces/perl5 doesn't build unless I add an include of "c.h" in Pg.c. ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
