[GENERAL] Postgres and bambooinvoice
Hi all, Was wondering if someone has experience with bommbooinvoice and postgres as the backend. Hoping for some off list help if the combination does actually work. Thanks Allan The material contained in this email is confidential and may be subject to legal privilege and/or copyright. Please do not re-transmit, distribute, copy or commercialise any of the material in this message unless you are authorised to do so. If you are not the intended recipient, use, disclosure or copying of this information is prohibited. If you have received this document in error, please advise the sender and delete the document. None of Arrium Limited, its related bodies corporate or the sender accept responsibility for any viruses contained in this email or any attachments. All and any rights as to confidentiality, legal professional privilege and copyright are expressly reserved. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Should casting to integer produce same result as trunc()
-Original Message- From: Alban Hertroys [mailto:haram...@gmail.com] Sent: Wednesday, 12 October 2011 1:35 AM To: Tom Lane Cc: Harvey, Allan AC; pgsql-general@postgresql.org Subject: Re: [GENERAL] Should casting to integer produce same result as trunc() On 11 October 2011 15:41, Tom Lane t...@sss.pgh.pa.us wrote: Alban Hertroys haram...@gmail.com writes: On 11 Oct 2011, at 2:55, Harvey, Allan AC wrote: My simple understanding of trunc() and casting to an integer says that there is a bug here. Which the type-cast should round to 4380103 and 4380104 respectively. It doesn't: That's because a cast from float to int rounds, it doesn't truncate. regression=# select (4.7::float8)::int; int4 -- 5 (1 row) I figured it would be something like that. Is that how it's defined in the SQL standard? All other programming languages I've come to know truncate floats on such casts (C, php, python, java, to name a few). This is probably quite surprising to people used to these languages. Thanks guys, yes C/C++ is my poison, forming my expectations. Allan The material contained in this email may be confidential, privileged or copyrighted. If you are not the intended recipient, use, disclosure or copying of this information is prohibited. If you have received this document in error, please advise the sender and delete the document. Neither OneSteel nor the sender accept responsibility for any viruses contained in this email or any attachments. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Should casting to integer produce same result as trunc()
Hi all, Had to squash timestamps to the nearest 5 minutes and things went wrong. My simple understanding of trunc() and casting to an integer says that there is a bug here. Expect it is my understanding though. Can someone set me straight? And thank you all for a wonderfull RDBMS. Allan select version(); version - PostgreSQL 8.2.4 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 4.1.0 (SUSE Linux) (1 row) -- trying to squash timestamps to five minutes. The result should be different. Use casting. select timestamp without time zone 'epoch' + (((extract( epoch from '2011-08-22 08:37:30'::timestamp ) + 10 * 3600) / 300 )::integer) * 300 * INTERVAL '1 second', timestamp without time zone 'epoch' + (((extract( epoch from '2011-08-22 08:42:30'::timestamp ) + 10 * 3600) / 300 )::integer) * 300 * INTERVAL '1 second'; ?column? | ?column? -+- 2011-08-22 08:40:00 | 2011-08-22 08:40:00 (1 row) -- The result is different, correct. Use trunc(). select timestamp without time zone 'epoch' + trunc(((extract( epoch from '2011-08-22 08:37:30'::timestamp ) + 10 * 3600) / 300 )) * 300 * INTERVAL '1 sec ond', timestamp without time zone 'epoch' + trunc(((extract( epoch from '2011-08-22 08:42:30'::timestamp ) + 10 * 3600) / 300 )) * 300 * INTERVAL '1 seco nd'; ?column? | ?column? -+- 2011-08-22 08:35:00 | 2011-08-22 08:40:00 (1 row) -- Raw seconds. Different as expected. select (((extract( epoch from '2011-08-22 08:37:30'::timestamp ) + 10 * 3600) )::integer), (((extract( epoch from '2011-08-22 08:42:30'::timestamp ) + 10 * 3600) )::integer); int4|int4 + 1314002250 | 1314002550 (1 row) -- should be different but are not. select (((extract( epoch from '2011-08-22 08:37:30'::timestamp ) + 10 * 3600) / 300 )::integer), (((extract( epoch from '2011-08-22 08:42:30'::timestamp ) + 10 * 3600) / 300 )::integer); int4 | int4 -+- 4380008 | 4380008 (1 row) select (1314002250 / 300)::integer, (1314002550 / 300)::integer; int4 | int4 -+- 4380007 | 4380008 (1 row) The material contained in this email may be confidential, privileged or copyrighted. If you are not the intended recipient, use, disclosure or copying of this information is prohibited. If you have received this document in error, please advise the sender and delete the document. Neither OneSteel nor the sender accept responsibility for any viruses contained in this email or any attachments. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] SQL Syntax - like FIELD and BITPATTERN = BITPATTERN?
I do not know how to really describe this... In pascal I would do If (AField and Flag3)=Flag3 then ... Checking to see if AField contains the BIT(s) FLAG3 is set to... is there a way to do this in a SELECT statement -- select * from ATable where AField and 0x0004=0x0004 ? Thanks, O. perhaps select * from ATable where AField::bit(16) B'0100' = B'0100'; Allan The material contained in this email may be confidential, privileged or copyrighted. If you are not the intended recipient, use, disclosure or copying of this information is prohibited. If you have received this document in error, please advise the sender and delete the document. Neither OneSteel nor the sender accept responsibility for any viruses contained in this email or any attachments. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Is there a function for Converting a Decimal into BINARY ?
Eg: I have a datatype in the DB which needs to be converted. DEC = 192 BINARY = 1100 How about this gwmdb= select 192::bit(16); bit -- 1100 (1 row) Hope that helps Allan The material contained in this email may be confidential, privileged or copyrighted. If you are not the intended recipient, use, disclosure or copying of this information is prohibited. If you have received this document in error, please advise the sender and delete the document. Neither OneSteel nor the sender accept responsibility for any viruses contained in this email or any attachments. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: FW: [GENERAL] how many connections can i use????
On Fri, May 8, 2009 at 10:08 AM, Edmundo Robles L. erob...@sensacd.com.mx wrote: Operative system: SCO OpenServer 5.0.7 -- double egad (and a bit at odds with your sig...) jeje :-) yes , this is because in my work we use SCO but personally i use ubuntu on my desktop pc and laptop. This would be one instance where you could quite possibly convince someone that moving to something other than SCO with an old version of pgsql would be a (very very) good idea. FYI I have managed to get 8.3.3 compiled for SCO. cm3= select version(); version -- PostgreSQL 8.3.3 on i386-pc-sco3.2v5.0.7, compiled by GCC 2.95.3 (1 row) Allan The material contained in this email may be confidential, privileged or copyrighted. If you are not the intended recipient, use, disclosure or copying of this information is prohibited. If you have received this document in error, please advise the sender and delete the document. Neither OneSteel nor the sender accept responsibility for any viruses contained in this email or any attachments. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] LISTEN/NOTIFY problem
-Original Message-
From: pgsql-general-ow...@postgresql.org
[mailto:pgsql-general-ow...@postgresql.org]on Behalf Of Dmitri Girski
Sent: Monday, 23 March 2009 10:00 PM
To: pgsql-general@postgresql.org
Subject: [GENERAL] LISTEN/NOTIFY problem
Hi everybody,
I've got a weird problem with LISTEN/NOTIFY.
My C++ app subscribes for the notifications, just like in libpq examples:
http://www.postgresql.org/docs/8.3/static/libpq-example.html
The only difference, that I am setting the timeout on select just to check if
application wants to exit.
//open session
//subscribe
while(!exit)
{
sock = PGsocket(conn)
res = select(sock);
if (res)
//check if it timeout
//or if there was a notification
}
The problem that I am facing is that after some time notifications stop coming.
select() returns on timeout and nothing else.
Just as a test a included UNLISTEN/LISTEN sequence into the loop, so after
timeout or event application re-subscribes. And this helps.
The question is, what I am doing wrong with the code in the first place?
Any help is appreciated.
Cheers,
Dmitri.
--
@Gmail
Cannot tell what is wrong with you piece of code.
But below is a routine I use for notifications.
No problems with it so far.
Hope it helps.
Allan
/*
** Structure to hold the connection data
*/
typedef struct _condetails
{
char *pghost;
char *pgport;
char *pgoptions;
char *pgtty;
char *dbname;
char *pguser;
char *pgpswd;
PGconn *conn;
int bpid;
} CONDETAILS;
/*
** Register for a database notification
*/
int reg_notification( CONDETAILS *cd, const char *notif )
{
char *sqlnotify, sql[128];
PGconn* conn;
PGresult* res;
conn = cd-conn;
sqlnotify = listen %s;
/*
** check to see that the backend connection was successfully made
*/
if ( PQstatus( conn ) == CONNECTION_BAD )
{
sysErr( reg_notification(). %s, PQerrorMessage( conn ) );
PQfinish( conn );
condetails( cd );
return -1;
}
/*
** Register
*/
sprintf( sql, sqlnotify, notif );
res = PQexec( conn, sql );
if ( PQresultStatus( res ) != PGRES_COMMAND_OK )
{
sysErr( reg_notification(). listen command failed. %s,
PQerrorMessage( conn ) );
PQclear( res );
return -1;
}
PQclear( res );
return 0;
}
/*
** Wait for a database notification or time out
** This is an async method.
**
** Return only the first notification not generated by me
** and flush the rest.
**
** Probly should do something smarter.
*/
int wait_db_notification( CONDETAILS *cd, char *notif, const long sec, const
long usec )
{
int sock, ret;
fd_set input_mask;
PGconn* conn;
PGnotify *notify;
struct timeval timeout;
conn = cd-conn;
/*
** check to see that the backend connection was successfully made
*/
if ( PQstatus( conn ) == CONNECTION_BAD )
{
sysErr( wait_db_notification(). %s, PQerrorMessage( conn ) );
PQfinish( conn );
condetails( cd );
return -1;
}
sock = PQsocket( conn );
if ( sock 0 )
{
sysErr( wait_db_notification(). Could not get socket
descriptor. %s, PQerrorMessage( conn ) );
return -1;
}
FD_ZERO( input_mask );
FD_SET( sock, input_mask );
timeout.tv_sec = sec;
timeout.tv_usec = usec;
ret = select( sock + 1, input_mask, NULL, NULL, timeout );
if ( ret 0 )
{
sysErr( %d: %s, __LINE__, wait_db_notification(). Select on
db socket failed );
if ( errno != EINTR )
exit( 1 );
/*
** Been interrupted by a trappable signal
** Force going to the top of the loop to handle it.
-*/
return -1;
}
if ( ret == 0 )
{
/*
** Time out occurred.
*/
return 0;
}
/*
** Some activity on the db
*/
ret = -1;
PQconsumeInput( conn );
do
{
notify = PQnotifies( conn );
if ( notify != NULL )
{
if ( cd-bpid != notify-be_pid )
{
strcpy( notif, notify-relname );
ret = 1;
Re: [GENERAL] bash postgres
-Original Message- From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general-ow...@postgresql.org]on Behalf Of Greenhorn Sent: Monday, 23 March 2009 3:03 PM To: pgsql-general@postgresql.org; pgsql-...@postgresql.org Subject: [GENERAL] bash postgres Hi, I'm trying to pass variables on a bash script embedded with psql commands. cat header.txt to1,from1,subject1 to2,from2,subject2 to3,from3,subject3 to4,from4,subject4 cat b.sh #!/bin/bash two=2 psql -h localhost -U postgres -d mobile -c create temp table header ( field_1 textnot null, field_2 textnot null, field_3 textnot null ); \\copy header FROM header.txt CSV SELECT * FROM header limit $two; When I execute b.sh ERROR: syntax error at or near \ LINE 10: \copy header FROM header.txt CSV ^ How do I use \c (or any other psql commands beginning with a \) in a bash script? Thanks. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general #!/bin/bash two=2 Try something like psql -h localhost -U postgres -d mobile ENDOFSQL create temp table header ( field_1 textnot null, field_2 textnot null, field_3 textnot null ); \copy header FROM header.txt CSV SELECT * FROM header limit $two; ENDOFSQL The material contained in this email may be confidential, privileged or copyrighted. If you are not the intended recipient, use, disclosure or copying of this information is prohibited. If you have received this document in error, please advise the sender and delete the document. Neither OneSteel nor the sender accept responsibility for any viruses contained in this email or any attachments. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] tuples
I have a question concerning psql. I found that psql has a defined command '-t' and that it turns off printing of column names and result row count footers, etc. what I look for, is a command, which would turn off result row count footer, but would print column names. is there an easy way to do this? Start psql with the -P footer switch. 8.3.3 works this way. Allan The material contained in this email may be confidential, privileged or copyrighted. If you are not the intended recipient, use, disclosure or copying of this information is prohibited. If you have received this document in error, please advise the sender and delete the document. Neither OneSteel nor the sender accept responsibility for any viruses contained in this email or any attachments. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] How to echo statements in sourced file?
right in my psql terminal after each statement in the file gets executed. But now I need a way to have these statements that are being timed themselves echoed to the terminal. Is there a way to do this? start psql with the -a switch, I think. Allan The material contained in this email may be confidential, privileged or copyrighted. If you are not the intended recipient, use, disclosure or copying of this information is prohibited. If you have received this document in error, please advise the sender and delete the document. Neither OneSteel nor the sender accept responsibility for any viruses contained in this email or any attachments.
Re: [GENERAL] inserting only new rows from csv file
-Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Behalf Of Scott Marlowe Sent: Thursday, 2 October 2008 7:39 AM To: Seb Cc: pgsql-general@postgresql.org Subject: Re: [GENERAL] inserting only new rows from csv file On Wed, Oct 1, 2008 at 3:16 PM, Seb [EMAIL PROTECTED] wrote: Hi, I've seen some approaches to this problem, but I suspect there are better ways. A csv file is produced nightly, with the same table structure as a table in a PostgreSQL database. The file grows over time as new records are added every day. However, the rows in the file may not be sorted the same way every day. I need to isolate the new rows and insert them into the database table. Before I start writing a script to do this, I need to decide whether to do the comparison between the database table and the csv file: a) within the database, or b) COPY'ing the table to a file and then do the comparison. I'm not very experienced with SQL, so am more comfortable with the latter option using plain shell and awk. Which route is best? If a), I'd appreciate some SQL code to do it. Thanks. I'd load them into a staging table, and use a query (either a subselect or a left join where null type query) to load them into the master table. I solved the same problem this way. csv file processed by gawk script to produce SQL script with inserts etc. Then pass the SQL file to psql. This is what I have in my BEGIN line of my gawk script leaving out appropriate print statements for clarity. CREATE TEMPORARY TABLE tempjobdetails( LIKE jobdetails INCLUDING DEFAULTS ); --Do load from csv into tempjobdetails This is in my END line of the gawk scrip ditto the print statements. insert into jobdetails ( select * from tempjobdetails where ( tempjobdetails.jobnum, tempjobdetails.opnum ) not in ( select jobdetails.jobnum, jobdetails.opnum from jobdetails ) ); Hope this helps. Allan The material contained in this email may be confidential, privileged or copyrighted. If you are not the intended recipient, use, disclosure or copying of this information is prohibited. If you have received this document in error, please advise the sender and delete the document. Neither OneSteel nor the sender accept responsibility for any viruses contained in this email or any attachments. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] 8.3.3 regression test on SCO 5.0.7
Harvey, Allan AC [EMAIL PROTECTED] writes: Harvey, Allan AC [EMAIL PROTECTED] writes: creating template1 database in /upgrades/postgres/postgresql-8.3.3/src/test/regress/./tmp_che ck/data/base/1 ... =: is not an identifier A diff of postgres.bki on the SCO system to another that completed the regression tests OK produced no differences. Huh ... so much for the easy theory. What you need to do next is figure out exactly where the failure is coming from. Try running initdb with --debug option (this will produce LOTS of stuff on stderr), and post the last hundred or so lines of debug output. regards, tom lane This is mainly for the archives should someone else like to beat SCO into partial submission Regression testing does not work for reasons I don't know when attempting them from the makefiles. ie make check. BUT screen scrapping the executed commands and/or suggestions does work. Here is what worked ( scrapped from bash history, hope I go it right ). Substitute you own installation path. cd src/test/regress rm -r /upgrades/postgres/postgresql-8.3.3/src/test/regress/./tmp_check/data /upgrades/postgres/postgresql-8.3.3/src/test/regress/./tmp_check/install//usr/local/pgsql/bin/initdb -D /upgrades/postgres/postgresql-8.3.3/src/test/regress/./tmp_check/data -L /upgrades/postgres/postgresql-8.3.3/src/test/regress/./tmp_check/install/usr/local/pgsql/share --noclean --no-locale /upgrades/postgres/postgresql-8.3.3/src/test/regress/./tmp_check/install//usr/local/pgsql/bin/postgres -D /upgrades/postgres/postgresql-8.3.3/src/test/regress/./tmp_check/data In another shell... cd src/test/regress ./pg_regress --psqldir=/upgrades/postgres/postgresql-8.3.3/src/test/regress/./tmp_check/install//usr/local/pgsql/bin --schedule=./serial_schedule --srcdir=/upgrades/postgres/postgresql-8.3.3/src/test/regress --multibyte=SQL_ASCII --load-language=plpgsql --host=localhost All tests passed, save for those that use Infinity and NaN and variants on that theme. I can post regression.diffs should anyone want them. Regression testing is however the end of the story, getting it compiled was the main effort. Summarised here: Get GNU gawk and tar and substitute the SCO distributed versions. Do not use the SCO Open server development environment ( export CC=cc; ./configure; etc etc ) as 64 bit is not supported an even more regression tests fail. The Unixware and OpenServer Development Kit compiles nicely, but promptly core dumps with unsupported system calls in the compatibility module or some such. Attempting to use the Skunkware gcc (2.95.3) fails in ./configure ( export CC=gcc ). With the help of http://archives.postgresql.org/pgsql-bugs/2001-06/msg00141.php and http://archives.postgresql.org/pgsql-ports/2008-06/msg1.php The correct options can be given to gcc. src/makefiles/Makefile.sco is the wrong place, see link above, /src/template/sco is the right place and I made it #CC=$CC -b elf CC=$CC -melf The make worked which lead to the regression testing, see above. Note UNIX sockets seem broken for get/setsockopt() so always connect over TCP/IP. ie --host=localhost seems to work around it. Hope this helps someone else. Now to the actuall upgrade. Allan The material contained in this email may be confidential, privileged or copyrighted. If you are not the intended recipient, use, disclosure or copying of this information is prohibited. If you have received this document in error, please advise the sender and delete the document. Neither OneSteel nor the sender accept responsibility for any viruses contained in this email or any attachments. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] 8.3.3 regression test on SCO 5.0.7
Harvey, Allan AC [EMAIL PROTECTED] writes: creating template1 database in /upgrades/postgres/postgresql-8.3.3/src/test/regre ss/./tmp_check/data/base/1 ... =: is not an identifier I'd guess you have an incompatible awk or possibly sed. Look at the postgres.bki file generated on this system and compare it to one generated from 8.3 on a non-broken system. It might or might not be easy to determine exactly where the breakage is, but I suspect the short answer is going to be install gawk. regards, tom lane A diff of postgres.bki on the SCO system to another that completed the regression tests OK produced no differences. Slapped GNU Awk 3.1.1 over the top of the SCO distributed awk. No difference. Looking into sed now. Ta, Allan The material contained in this email may be confidential, privileged or copyrighted. If you are not the intended recipient, use, disclosure or copying of this information is prohibited. If you have received this document in error, please advise the sender and delete the document. Neither OneSteel nor the sender accept responsibility for any viruses contained in this email or any attachments. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] 8.3.3 regression test on SCO 5.0.7
Harvey, Allan AC [EMAIL PROTECTED] writes: Harvey, Allan AC [EMAIL PROTECTED] writes: creating template1 database in /upgrades/postgres/postgresql-8.3.3/src/test/regress/./tmp_che ck/data/base/1 ... =: is not an identifier A diff of postgres.bki on the SCO system to another that completed the regression tests OK produced no differences. Huh ... so much for the easy theory. What you need to do next is figure out exactly where the failure is coming from. Try running initdb with --debug option (this will produce LOTS of stuff on stderr), and post the last hundred or so lines of debug output. I don't think this is what you were expecting, I know I was not, but then I don't know much. Allan DEBUG: StartTransaction DEBUG: name: unnamed; blockState: DEFAULT; state: INPROGR, xid/subid/cid: 0/1/0, nestlvl: 1, children: DEBUG: start transaction DEBUG: CommitTransaction DEBUG: name: unnamed; blockState: STARTED; state: INPROGR, xid/subid/cid: 0/1/35, nestlvl: 1, children: DEBUG: commit transaction DEBUG: proc_exit(0) DEBUG: shmem_exit(0) DEBUG: exit(0) ok initializing pg_authid ... ok initializing dependencies ... ok creating system views ... ok loading system objects' descriptions ... ok creating conversions ... ok creating dictionaries ... ok setting privileges on built-in objects ... ok creating information schema ... ok vacuuming database template1 ... ok copying template1 to template0 ... ok copying template1 to postgres ... ok WARNING: enabling trust authentication for local connections You can change this by editing pg_hba.conf or using the -A option the next time you run initdb. Success. You can now start the database server using: /upgrades/postgres/postgresql-8.3.3/src/test/regress/./tmp_check/install//usr/local/pgsql/bin/postgres -D /upgrades/postgres/postgresql-8.3.3/src/test/regress/./tmp_check/data or /upgrades/postgres/postgresql-8.3.3/src/test/regress/./tmp_check/install//usr/local/pgsql/bin/pg_ctl -D /upgrades/postgres/postgresql-8.3.3/src/test/regress/./tmp_check/data -l logfile start The material contained in this email may be confidential, privileged or copyrighted. If you are not the intended recipient, use, disclosure or copying of this information is prohibited. If you have received this document in error, please advise the sender and delete the document. Neither OneSteel nor the sender accept responsibility for any viruses contained in this email or any attachments. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] 8.3.3 Complie issue
Old Slackware? If you really want to compile there, I think it should work by just removing the -Wl,--version-script param from the link line. Thanks Alvaro, Worked through them. Regression tests show all is OK, save for the handling of Infinity. Don't think that will be a problem 'cause I never go there. Allan The material contained in this email may be confidential, privileged or copyrighted. If you are not the intended recipient, use, disclosure or copying of this information is prohibited. If you have received this document in error, please advise the sender and delete the document. Neither OneSteel nor the sender accept responsibility for any viruses contained in this email or any attachments. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] 8.3.3 regression test on SCO 5.0.7
Hi All, I have been working through upgrades on legacy business systems on old Linux and SCO plateforms. With the help of this list the Linux problems are handled, thanks. The SCO build is not yet done. I have managed to compile 8.3.3 with the help of the archives. But there were lots of warnings and I would like to see the results of the regression tests. I'm looking for help to make the regression tests happen. Thanks Allan make check at the top level fails with: Running in noclean mode. Mistakes will not be cleaned up. could not determine encoding for locale C_C.C: codeset is initdb: could not find suitable encoding for locale C_C.C Rerun initdb with the -E option. Try initdb --help for more information. The files belonging to this database system will be owned by user postgres. This user must also own the server process. The database cluster will be initialized with locale C_C.C. Moving to .../postgresql-8.3.3/src/test/regress and running ./pg_regress --temp-install=./tmp_check --top-builddir=../../.. --srcdir=/upgrades/postgres/postgresql-8.3.3/src/test/regress --temp-port=55432 --schedule=./parallel_schedule --multibyte=SQL_ASCII --load-language=plpgsql --no-locale ( note the added --no-locale ) gets me further, but fails with: Running in noclean mode. Mistakes will not be cleaned up. The files belonging to this database system will be owned by user postgres. This user must also own the server process. The database cluster will be initialized with locale C. The default database encoding has accordingly been set to SQL_ASCII. The default text search configuration will be set to english. creating directory /upgrades/postgres/postgresql-8.3.3/src/test/regress/./tmp_che ck/data ... ok creating subdirectories ... ok selecting default max_connections ... 100 selecting default shared_buffers/max_fsm_pages ... 32MB/204800 creating configuration files ... ok creating template1 database in /upgrades/postgres/postgresql-8.3.3/src/test/regre ss/./tmp_check/data/base/1 ... =: is not an identifier child process exited with exit code 1 initdb: data directory /upgrades/postgres/postgresql-8.3.3/src/test/regress/./tm p_check/data not removed at user's request The material contained in this email may be confidential, privileged or copyrighted. If you are not the intended recipient, use, disclosure or copying of this information is prohibited. If you have received this document in error, please advise the sender and delete the document. Neither OneSteel nor the sender accept responsibility for any viruses contained in this email or any attachments. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] 8.3.3 Complie issue
Hi All, Compile issue I was hoping someone might have a suggestion for: Did ./configure --prefix /removeExt2/pgsql8.3 make make[3]: Entering directory `/removeExt2/postgresql-8.3.3/src/backend/utils/mb/conversion_procs/utf8_and_euc_jis_2004' make[3]: *** No rule to make target `utf8_and_euc_jis_2004.o', needed by `libutf8_and_euc_jis_2004.so.0.0'. Stop. make[3]: Leaving directory `/removeExt2/postgresql-8.3.3/src/backend/utils/mb/conversion_procs/utf8_and_euc_jis_2004' Allan The material contained in this email may be confidential, privileged or copyrighted. If you are not the intended recipient, use, disclosure or copying of this information is prohibited. If you have received this document in error, please advise the sender and delete the document. Neither OneSteel nor the sender accept responsibility for any viruses contained in this email or any attachments. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] 8.3.3 Complie issue
I think we've seen this before: do you have utf8_and_euc_jis_2004.c
in that directory? There seem to be some broken versions of tar out
there that can't deal with extracting such a long file name from the
distribution tarball.
Thanks Tom,
The c on the end was missing.
Fixed that, how about this
echo '{ global:' exports.list
gawk '/^[^#]/ {printf %s;\n,$1}' exports.txt exports.list
echo ' local: *; };' exports.list
gcc -O2 -Wall -Wmissing-prototypes -Wpointer-arith -Winline
-fno-strict-aliasing -fpic -shared -Wl,-soname,libpq.so.5
-Wl,--version-script=exports.list fe-auth.o fe-connect.o fe-exec.o fe-misc.o
fe-print.o fe-lobj.o fe-protocol2.o fe-protocol3.o pqexpbuffer.o pqsignal.o
fe-secure.o md5.o ip.o wchar.o encnames.o noblock.o pgstrcasecmp.o thread.o
strlcpy.o -L../../../src/port -lcrypt -Wl,-rpath,'/removeExt2/pgsql8.3/lib' -o
libpq.so.5.1
/usr/i386-slackware-linux/bin/ld:exports.list:1: parse error in VERSION script
collect2: ld returned 1 exit status
make[3]: *** [libpq.so.5.1] Error 1
The material contained in this email may be confidential, privileged or
copyrighted. If you are not the intended recipient, use, disclosure or copying
of this information is prohibited. If you have received this document in error,
please advise the sender and delete the document. Neither OneSteel nor the
sender accept responsibility for any viruses contained in this email or any
attachments.
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Updating
Would it be possible to get an example of such coding?? This trigger has an argument passed. When the trigger is assigned I know whether the column is of type txt or float. It uses the column name to determine what to do. Hope this helps Allan create or replace function insert_if_diff() returns trigger as $BODY$ declare r record; begin for r in execute 'select ' || TG_ARGV[0] || ' from ' || TG_TABLE_NAME || ' order by dt desc limit 1;' loop if TG_ARGV[0] = 'value' then if new.value = r.value then return null; end if; end if; if TG_ARGV[0] = 'txt' then if new.txt = r.txt then return null; end if; end if; return new; end loop; return NEW; end; $BODY$ language plpgsql; The material contained in this email may be confidential, privileged or copyrighted. If you are not the intended recipient, use, disclosure or copying of this information is prohibited. If you have received this document in error, please advise the sender and delete the document. Neither OneSteel nor the sender accept responsibility for any viruses contained in this email or any attachments. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] PostgresSQL vs Ingress
Ow Mun Henq wrote:- Ingress is also an open source RDBM (and DataWarehouseing) and I'm wondering if anyone here has anything to say about it. They also offer community editions but I've not gone to see how much it differs/offers compared to PG. I've tried to DL the community edition, but upon log-in, I only get a blank page. (tried on both firefox and opera) Our business has been using Ingres since 1990 and still do. It is a top quality product and we have followed it as it changed to an open source product. http://www.ingres.com/downloads/prod-comm-download.php What keeps Ingres in favour here ( amounst the other developers ), for good or bad, is QBF and Vision. I favour Postgres because of psql and its ease of use in Bash scripts. I moved to Postgres originally because I the source was available to cross compile libpq to OS9. Hope this helps. Allan The material contained in this email may be confidential, privileged or copyrighted. If you are not the intended recipient, use, disclosure or copying of this information is prohibited. If you have received this document in error, please advise the sender and delete the document. Neither OneSteel nor the sender accept responsibility for any viruses contained in this email or any attachments. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] looking for some real world performance numbers
As far as real world numbers, we have a data-intensive app (network data collection for a telecom company) that is currently inserting about 16 million rows a day. I benchmarked PG for that app and with some tweaking, PG could handle it. Me too, not telco though. 5.5 million per day across 2240 tables and 4 databases with 50 days on-line. Tweaking to auto vaccuum to get it to keep up with the daily deletes and fsync off, slow disks not PG's fault but have UPS. Allan The material contained in this email may be confidential, privileged or copyrighted. If you are not the intended recipient, use, disclosure or copying of this information is prohibited. If you have received this document in error, please advise the sender and delete the document. Neither OneSteel nor the sender accept responsibility for any viruses contained in this email or any attachments. ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Excell
Because I'm delivering reports to dozens of people who have windows, no psql client, and just want to go to a web page, click a button, and get their report (or was that a banana?) I do exactly this with bog basic HTML and bash scripts. Can send you a copy if you want examples. Allan The material contained in this email may be confidential, privileged or copyrighted. If you are not the intended recipient, use, disclosure or copying of this information is prohibited. If you have received this document in error, please advise the sender and delete the document. Neither OneSteel nor the sender accept responsibility for any viruses contained in this email or any attachments. ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Trigger function that works with both updates and deletes?
Second, while I could write two trigger functions, one dealing with add/update, the other with deletes, it's probably neater to have a single trigger function and have it discriminate am I being called for a delete, or an add/update? I don't know how to determine the type record change. I use trigger arguments and determine what to do by: if TG_ARGV[0] = 'value' then end if; Allan The material contained in this email may be confidential, privileged or copyrighted. If you are not the intended recipient, use, disclosure or copying of this information is prohibited. If you have received this document in error, please advise the sender and delete the document. Neither OneSteel nor the sender accept responsibility for any viruses contained in this email or any attachments. ---(end of broadcast)--- TIP 6: explain analyze is your friend
[GENERAL] 'Expensive' column in result set
Hi all, I have a select that involves a column in the result set that is expensive to get. I need to use the expensive column in a subsequent calculated column. Is there a syntax to run the expensive function once only or does it just have to be done, blk_speed() is the expensive function. Example of what I would like to do, does not work of course: select b.block, b.p_code, p.description, p.blk_speed as set, blk_speed( b.block ) as actual, actual / set * 100 as Speed % from block b, product p where b.p_code = p.p_code; This does work select b.block, b.p_code, p.description, p.blk_speed as set, blk_speed( b.block ) as actual, blk_speed( b.block ) / ( p.blk_speed + 0.0001 ) * 100 as Speed % from block b, product p where b.p_code = p.p_code; OR is postgres smart enough to know it only needs to get blk_speed() once. Thanks Allan The material contained in this email may be confidential, privileged or copyrighted. If you are not the intended recipient, use, disclosure or copying of this information is prohibited. If you have received this document in error, please advise the sender and delete the document. Neither OneSteel nor the sender accept responsibility for any viruses contained in this email or any attachments. ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] 'Expensive' column in result set
Tom, Thank you. I shall experiment. Harvey, Allan AC [EMAIL PROTECTED] writes: select b.block, b.p_code, p.description, p.blk_speed as set, blk_speed( b.block ) as actual, blk_speed( b.block ) / ( p.blk_speed + 0.0001 ) * 100 as Speed % from block b, product p where b.p_code = p.p_code; OR is postgres smart enough to know it only needs to get blk_speed() once. No, PG will not stop to notice the common subexpression. (Searching for such would eat more cycles than it saves, on average.) What you can do is use a two-level select: select block, p_code, description, col as set, func as actual, func / ( col + 0.0001 ) * 100 as Speed % from (select b.block, b.p_code, p.description, p.blk_speed as col, blk_speed( b.block ) as func from block b, product p where b.p_code = p.p_code) as ss; (The example would've been clearer if you'd not used the same name for both a column and a function; but I digress.) Now as this is written, the optimizer is likely to flatten the two-level select into one level and thereby copy the blk_speed function call into two places, which you don't want. The best workaround for that is to add offset 0 to the sub-select. Another possibility (as of PG 8.2) is to mark the function as volatile --- but that might prevent some optimizations that you would like to happen, so it's probably not the best answer. regards, tom lane The material contained in this email may be confidential, privileged or copyrighted. If you are not the intended recipient, use, disclosure or copying of this information is prohibited. If you have received this document in error, please advise the sender and delete the document. Neither OneSteel nor the sender accept responsibility for any viruses contained in this email or any attachments. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] selective export for subsequent import (COPY)
Chris, I know about: psql dbname -tc select * from tableX where whatever tableX.dat What about psql $DATABASEHOST -U $DATABASEUSER -d $DATABASE -q -P footer -A -F , -c $DETAIL_SQL table.csv To produce a comma separated file of tuples only. If I'm not mistaken, as happens quite a bit, you can then use COPY to import the csv file. Allan The material contained in this email may be confidential, privileged or copyrighted. If you are not the intended recipient, use, disclosure or copying of this information is prohibited. If you have received this document in error, please advise the sender and delete the document. Neither OneSteel nor the sender accept responsibility for any viruses contained in this email or any attachments. ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Storing blobs in PG DB
I find it fine. Bit different usage though. I store about 200 50MB items. Allan Hi All! Question is : How suitable PG for storing about 2 000 000 binary files 0,5-2,0 Mb size each ? It is not planned the big number of clients or a plenty of updatings. Like photoalbum on local host. The material contained in this email may be confidential, privileged or copyrighted. If you are not the intended recipient, use, disclosure or copying of this information is prohibited. If you have received this document in error, please advise the sender and delete the document. Neither OneSteel nor the sender accept responsibility for any viruses contained in this email or any attachments. ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] PGSQL with high number of database rows?
Tim, massive, around 20,000 new rows in one of the tables per day. As an example... I'm doing about 4000 inserts spread across about 1800 tables per minute. Pisses it in with fsync off and the PC ( IBM x3650 1 CPU, 1 Gig memory ) on a UPS. Allan The material contained in this email may be confidential, privileged or copyrighted. If you are not the intended recipient, use, disclosure or copying of this information is prohibited. If you have received this document in error, please advise the sender and delete the document. Neither OneSteel nor the sender accept responsibility for any viruses contained in this email or any attachments. ---(end of broadcast)--- TIP 6: explain analyze is your friend
[GENERAL] Multiple atributes with -P switch to psql
Hi all, Trying to get psql to produce multiple table attributes in the table tag when psql is used with the -H switch. eg TABLEOPTIONS=-P border=5 -P tableattr=width=\80%\ psql $DATABASEHOST -U $DATABASEUSER -d $DATABASE -q -P footer -P title=Summary -H $TABLEOPTIONS produces ... table border=5 width=80% ... As expected. TABLEOPTIONS=-P border=5 -P tableattr=width=\80%\ -P tableattr=bgcolor=\#FF\ produces ... table border=5 bgcolor=#FF ... Seems to hang on to the last. And TABLEOPTIONS=-P border=5 -P tableattr=\width=\80%\ bgcolor=\#FF\\ produces ... table border=5 width=80% ... My quoting of the quotes is wrong but the point is that the quote to encapsulate the white space for the tablattr is placed into the HTML produced. It maybe that you can only pass one table attribute or that I'm missing something. I favour the latter. Calls are from within a bash script. Can anyone set me straight? version - PostgreSQL 8.2.0 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.3.3 (SuSE Linux) (1 row) Thanks Allan The material contained in this email may be confidential, privileged or copyrighted. If you are not the intended recipient, use, disclosure or copying of this information is prohibited. If you have received this document in error, please advise the sender and delete the document. Neither OneSteel nor the sender accept responsibility for any viruses contained in this email or any attachments. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Multiple atributes with -P switch to psql
Stuart, Thanks for the reply. You are dead right, and as expected the problem is not with psql. $ psql -H -P tableattr='width=80% bgcolor=#FF' works for me. Carefully study my nice simple quoting: single quotes '' around the whole tableattr='THING' shebang and non-escaped double quotes for width and bgcolor values. Cheers, Stuart. In my defence I did try that exact quoting, the problem is that it is being run from a script. I have thrown together an example to show the problem. I have not yet worked to a solution. If you know what it is I would be very greatful. Allan #!/bin/bash set -x DATABASEHOST=-h jitsnwm DATABASEUSER=galvuser DATABASE=galvdb TABLEOPTIONS=-P border=5 -P tableattr='width=\80%\ bgcolor=\#AFAFAF\' ED=/usr/local/pgsql/bin $ED/psql $DATABASEHOST -U $DATABASEUSER -d $DATABASE -q -P footer -P title=Summar y -H $TABLEOPTIONS EOF_SQL select version(); EOF_SQL Produces [EMAIL PROTECTED]:~/agl/sql ./z.sh + DATABASEHOST=-h jitsnwm + DATABASEUSER=galvuser + DATABASE=galvdb + TABLEOPTIONS=-P border=5 -P tableattr='width=80% bgcolor=#AFAFAF' + ED=/usr/local/pgsql/bin + /usr/local/pgsql/bin/psql -h jitsnwm -U galvuser -d galvdb -q -P footer -P title=Summary -H -P border=5 -P 'tableattr='\''width=80%' 'bgcolor=#AFAFAF'\''' table border=5 'width=80% captionSummary/caption tr th align=centerversion/th /tr tr valign=top td align=leftPostgreSQL 8.2.0 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.3.3 (SuSE Linux)/td /tr /table The material contained in this email may be confidential, privileged or copyrighted. If you are not the intended recipient, use, disclosure or copying of this information is prohibited. If you have received this document in error, please advise the sender and delete the document. Neither OneSteel nor the sender accept responsibility for any viruses contained in this email or any attachments. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[GENERAL] How can I list the function.
I know the function is there. What am I doing wrong? galvdb=# galvdb=# \df+ delete_old List of functions Result data type | Schema | Name | Argument data types | Owner | Language | Source code | Description --++--+-+---+--+-+- (0 rows) galvdb=# select delete_old(); delete_old 482 (1 row) galvdb=# select version(); version - PostgreSQL 8.2.0 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.3.3 (SuSE Linux) (1 row) galvdb=# The material contained in this email may be confidential, privileged or copyrighted. If you are not the intended recipient, use, disclosure or copying of this information is prohibited. If you have received this document in error, please advise the sender and delete the document. Neither OneSteel nor the sender accept responsibility for any viruses contained in this email or any attachments. ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] How can I list the function. SOLVED
Thank you all, I know the function is there. What am I doing wrong? Talking to a 8.2.0 server with a 7.4.5 client. Sorry for the noise, I sometimes get lost on which versions I have where of this wonderful software. Allan The material contained in this email may be confidential, privileged or copyrighted. If you are not the intended recipient, use, disclosure or copying of this information is prohibited. If you have received this document in error, please advise the sender and delete the document. Neither OneSteel nor the sender accept responsibility for any viruses contained in this email or any attachments. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] plpgsql dynamic queries and optional arguments
Curtis,
Here is an example function that uses dynamic sql.
I use it under 7.4.5
Hope this helps.
Allan
-- Function to delete old data out of the point tables.
-- tablename is a column in the points table that holds the name
-- of the table in which this points data is stored.
create or replace function delete_old() returns integer as '
declare
pt record;
count integer;
sql_str varchar(512);
begin
count := 0;
for pt in select * from points loop
sql_str := ''deleting from '' || pt.tablename || '' data older than
'' || pt.savefor::varchar || '' days'';
--raise notice ''%'', sql_str;
sql_str := ''delete from '' || pt.tablename || '' where dt (now()
- interval '' || pt.savefor::varchar || '' days)::timestamp;'';
execute sql_str;
count := count + 1;
end loop;
return count;
end;
' LANGUAGE plpgsql;
-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Behalf Of Curtis Scheer
Sent: Wednesday, 16 August 2006 3:22 AM
To: pgsql-general@postgresql.org
Subject: [GENERAL] plpgsql dynamic queries and optional arguments
I have a table that I would like to be able to retrieve information out of
based on a combination of multiple columns and I would like to be able to do
this through a plpgsql stored procedure. Right now I have multiple stored
procedures that I am calling based on the values parameter values I pass them
and I am using static sql. The problem with this is it doesn't scale as well as
I would like it to because when I add another column of information to the
table that needs to be used for retrieval it adds another level of combinations.
Also, when dealing with null values with static sql I use the same exact sql
statement except for the where clause containing the column1 is null versus
column1 = passedvalue. Anyways, I have made a simple example procedure and
table; any help would be greatly appreciated basically I would like to use
dynamic sql instead of static but I have unsuccessfully been able to retrieve
the results of a dynamic sql statement in a pgplsql procedure. Here is the
example table and stored procedure.
CREATE TABLE public.foo
(
fooid int4 NOT NULL DEFAULT nextval('foo_fooid_seq'::regclass),
foo_date timestamp NOT NULL,
footypeid int4 NOT NULL,
footext varchar,
CONSTRAINT pk_fooid PRIMARY KEY (fooid)
)
WITHOUT OIDS;
ALTER TABLE public.foo OWNER TO fro;
CREATE OR REPLACE FUNCTION public.get_nextfoo(pfoo_date timestamp, pfoovalue
int4, pfootext bpchar)
RETURNS SETOF public.foo AS
$BODY$DECLARE
rec foo%ROWTYPE;
BEGIN
if pfootext is null then
SELECT
*
INTO
rec
FROM
foo
WHERE
foo_date = pfoo_date
and foovalue = pfoovalue
and footext is null
For Update;
else
SELECT
*
INTO
rec
FROM
foo
WHERE
foo_date = pfoo_date
and foovalue = pfoovalue
and footext = pfootext
For Update;
end if;
RETURN NEXT rec;
return;
END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE;
ALTER FUNCTION public.get_nextfoo(pfoo_date timestamp, pfoovalue int4,
pfootext bpchar) OWNER TO fro;
insert into foo(foo_date,foovalue,footext) values('2006-08-15',1,'bar');
insert into foo(foo_date,foovalue,footext) values('2006-08-14',1,'bar');
insert into foo(foo_date,foovalue,footext) values('2006-08-15',2,'bar');
insert into foo(foo_date,foovalue,footext) values('2006-08-14',2,'bar');
insert into foo(foo_date,foovalue) values('2006-08-15',1);
insert into foo(foo_date,foovalue) values('2006-08-14',1);
insert into foo(foo_date,foovalue) values('2006-08-15',2);
insert into foo(foo_date,foovalue) values('2006-08-14',2);
Thanks,
Curtis
The material contained in this email may be confidential, privileged or
copyrighted. If you are not the intended recipient, use, disclosure or copying
of this information is prohibited. If you have received this document in error,
please advise the sender and delete the document. Neither OneSteel nor the
sender accept responsibility for any viruses contained in this email or any
attachments.
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Is PostgreSQL an easy choice for a large CMS?
The open source offerings of ingres, ingres R3, runs the Wire section of our business http://www.onesteel.com Allan Postgres than from MySQL. I am financing this myself. hence the apprehension about the cost. Is there another contender I should think about. The material contained in this email may be confidential, privileged or copyrighted. If you are not the intended recipient, use, disclosure or copying of this information is prohibited. If you have received this document in error, please advise the sender and delete the document. Neither OneSteel nor the sender accept responsibility for any viruses contained in this email or any attachments. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] deleting table content through command prompt
Try psql.exe -h localhost -U your_user -d your_database -c drop table your_tablename; Assumption: windows switches are the same as the *nix ones -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Behalf Of [EMAIL PROTECTED] Sent: Thursday, 20 April 2006 9:04 AM To: pgsql-general@postgresql.org Subject: [GENERAL] deleting table content through command prompt hi all, i am trying to create a database backup and restore windows batch file, which when run will do a database table backup and a corresponding table restore. now for table_usernames backup i run something like from the command prompt, pg_dump.exe -i -h localhost -p 5432 -U postgres -F t -v -f C:\usernames.backup -t table_usernames loginDetails now for db restore i run something like from the command prompt, pg_restore.exe -i -h localhost -p 5432 -U postgres -d loginDetails -a -t table_usernames -v C:\usernames.backup the problem is that after i do a backup i need to delete the table content and then do a restore. through pgadmin i would do DELETE FROM table_usernames. however i need to do this from the command prompt, like how i am doing above. it seems very obvious and simple, but i am unable to think of how i would do deletion of a table from the command prompt thanks for all feedback and help. ---(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 The material contained in this email may be confidential, privileged or copyrighted. If you are not the intended recipient, use, disclosure or copying of this information is prohibited. If you have received this document in error, please advise the sender and delete the document. Neither OneSteel nor the sender accept responsibility for any viruses contained in this email or any attachments. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[GENERAL] Help to realise function
Hi all, Can anyone offer suggestions on how to realise this function It is the $1 as the table name that is the problem. I'm using Version 7.4.5 create or replace function last_scan( varchar, varchar ) returns float as ' declare result float; begin result := value from $1 where parameter = $2 order by dt desc limit 1; return result; end; ' LANGUAGE plpgsql; Thanks Allan The material contained in this email may be confidential, privileged or copyrighted. If you are not the intended recipient, use, disclosure or copying of this information is prohibited. If you have received this document in error, please advise the sender and delete the document. Neither OneSteel nor the sender accept responsibility for any viruses contained in this email or any attachments. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[GENERAL] Is this possible.
Hi all, Can sombody please help me realise a function, the intent as described by... -- Function to create the table for a new point CREATE OR REPLACE FUNCTION make_table( varchar ) RETURNS VARCHAR AS ' CREATE TABLE $1( parameter varchar(8) NOT NULL, value float NOT NULL, dt timestamp NOT NULL ); CREATE INDEX $1_dtindex ON $1( dt ); SELECT $1; ' LANGUAGE SQL; I'm using 7.4.5. Thanks Allan The material contained in this email may be confidential, privileged or copyrighted. If you are not the intended recipient, use, disclosure or copying of this information is prohibited. If you have received this document in error, please advise the sender and delete the document. Neither OneSteel nor the sender accept responsibility for any viruses contained in this email or any attachments. ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] automating backup ?
Start-Control Panel-Scheduled Tasks -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Behalf Of Zlatko Matic Sent: Monday, 27 June 2005 9:21 AM To: Michael Fuhr Cc: pgsql-general@postgresql.org Subject: Re: [GENERAL] automating backup ? Thanks Fuhr. Anybody can tell me how to do it on Windows XP ? Thanks. - Original Message - From: Michael Fuhr [EMAIL PROTECTED] To: Zlatko Matic [EMAIL PROTECTED] Cc: pgsql-general@postgresql.org Sent: Sunday, June 26, 2005 2:59 PM Subject: Re: [GENERAL] automating backup ? On Sun, Jun 26, 2005 at 11:18:31AM +0200, Zlatko Matic wrote: How to automate backup, so that Postgres automatically backups, for example, once in a week ? Using the operating system's mechanism for scheduling jobs to run periodically. For example, cron on Unix-like systems. The same question about vacuum ? Same answer as above. See also contrib/pg_autovacuum. Concerning backup, how to prevent that someone makes a copy (for example pg_dumpall) of a database, then installs new instance of Postgres, create the same user acount that was the original owner and then restore the database. In that case all restrictions would be overriden, right ? All what restrictions? If you've granted users permission to select certain data, then they can copy that data to somewhere else and do whatever they like with it; but with the original data they can do only what you grant them permission to do. If you don't want users to copy data then don't grant them select privilege on it, and make sure they don't have database superuser or operating system superuser (administrator) privileges on the database server. -- Michael Fuhr http://www.fuhr.org/~mfuhr/ ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[GENERAL] Performance suggestions?
Hi all, I'm after suggestions on how to keep the initial performance of a system I've put together for longer than 2 minutes. I have a small table about 20 rows, a constant, that is receiving about 160 updates per second. The table is used to share gathered data to other process asynchronously. After 5 min it is 12 updates per second. Performance returns after a vacuum analyse. I'm using 7.4.5. This is the table structure Table public.lastscan Column |Type | Modifiers ---+-+--- pointnum | integer | not null parameter | character varying(8)| not null value | double precision| not null dt| timestamp without time zone | not null Indexes: lsindex btree (pointnum, parameter) Or should I just stick to saving the data, inserts seem to go on and on, and use a different IPC method. Ta Allan ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[GENERAL] SCO installation help
Hi all,
Hope someone might know the answer.
Trying to install 7.4.5 ('cause its what I have) from source on SCO OpenServer
5.0.7
The compile succeeds, all be it with warnings...
gmake install -- OK
create the data directories -- OK
/usr/local/pgsql/bin/initdb - D /usr/local/pgsql/data -- OK
/usr/local/pgsql/bin/postmaster -i -B 256 D /usr/local/pgsql/data -- OK
/usr/local/pgsql/bin/createdb test produces
LOG: setsockopt(TCP_NODELAY) failed: Protocol not available
createdb: could not connect ..
Thanks in advance
Allan
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [GENERAL] table configuration tweak for performance gain.
Michael, I don't mind at all. 3.244 ms compared to 15706.179 ms. A sizeable difference. Screen scraps follow. Allan mill2= set enable_seqscan=off\g SET mill2= explain analyse select count(*) from history where pointname = 'MILL2-SPEED' and dt (now() - interval '5 minutes')::timestamp\g QUERY PLAN -- Aggregate (cost=34815.05..34815.05 rows=1 width=0) (actual time=2.267..2.275 rows=1 loops=1) - Index Scan using dtindex on history (cost=0.00..34783.32 rows=12690 width=0) (actual time=1.931..1.931 rows=0 loops=1) Index Cond: (((pointname)::text = 'MILL2-SPEED'::text) AND (dt ((now() - '00:05:00'::interval))::timestamp without time zone)) Total runtime: 3.244 ms (4 rows) mill2= set enable_seqscan=on\g SET mill2= select count(*) from history where pointname = 'MILL2-SPEED' and dt (now() - interval '5 minutes')::timestamp\g count --- 0 (1 row) mill2= explain analyse select count(*) from history where pointname = 'MILL2-SPEED' and dt (now() - interval '5 minutes')::timestamp\g QUERY PLAN -- Aggregate (cost=10163.50..10163.50 rows=1 width=0) (actual time=15705.388..15705.395 rows=1 loops=1) - Seq Scan on history (cost=0.00..10131.77 rows=12690 width=0) (actual time=15705.286..15705.286 rows=0 loops=1) Filter: (((pointname)::text = 'MILL2-SPEED'::text) AND (dt ((now() - '00:05:00'::interval))::timestamp without time zone)) Total runtime: 15706.179 ms (4 rows) -Original Message- From: Michael Fuhr [mailto:[EMAIL PROTECTED] Sent: Wednesday, 17 November 2004 12:19 To: Harvey, Allan AC Cc: Tom Lane Subject: Re: [GENERAL] table configuration tweak for performance gain. On Wed, Nov 17, 2004 at 09:39:11AM +1100, Harvey, Allan AC wrote: The solution then was:- an index of the right columns. explicit, exact type casting ( I'm a casual ingres user, type casting is something I never need or think you can do) PostgreSQL 8.0 will allow cross-type index usage, making the explicit cast unnecessary. turning off enable_seqscan for specific queries seemed to help a bit too. Performance in general might improve if you address the planner's reasons for chosing an inefficient plan. Even though you're satisifed with performance now, would you mind posting the output of EXPLAIN ANALYZE select ... with enable_seqscan on and then with it off? If nothing else, an analysis might be educational for others. -- Michael Fuhr http://www.fuhr.org/~mfuhr/ ---(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: [GENERAL] table configuration tweak for performance gain.
Tom, Michael, Thanks for your interests. My original post was scant on detail as I was unsure if I had found the right place. It appears I have, so... Version is 7.4.5 Table size these tests were carried out on:- mill2= select count(*) from history\g count 258606 (1 row) Before index:- mill2= \d history Table public.history Column |Type | Modifiers ---+-+--- pointname | character varying(32) | not null parameter | character varying(8)| not null value | double precision| not null dt| timestamp without time zone | not null snip. WARNING: skipping pg_conversion --- only table or database owner can analyze it WARNING: skipping pg_depend --- only table or database owner can analyze it ANALYZE mill2= explain select value from history where pointname = 'MILL2-SPEED' and dt now() - interval '5 minutes'\g QUERY PLAN - Seq Scan on history (cost=0.00..8276.82 rows=8982 width=8) Filter: (((pointname)::text = 'MILL2-SPEED'::text) AND ((dt)::timestamp with time zone (now() - '00:05:00'::interval))) (2 rows) After index:- mill2= create index dtindex on history( dt )\g CREATE INDEX mill2= \d history Table public.history Column |Type | Modifiers ---+-+--- pointname | character varying(32) | not null parameter | character varying(8)| not null value | double precision| not null dt| timestamp without time zone | not null Indexes: dtindex btree (dt) snip WARNING: skipping pg_conversion --- only table or database owner can analyze it WARNING: skipping pg_depend --- only table or database owner can analyze it ANALYZE mill2= explain select value from history where pointname = 'MILL2-SPEED' and dt now() - interval '5 minutes'\g QUERY PLAN - Seq Scan on history (cost=0.00..8263.19 rows=9342 width=8) Filter: (((pointname)::text = 'MILL2-SPEED'::text) AND ((dt)::timestamp with time zone (now() - '00:05:00'::interval))) (2 rows) don't recommend turning off enable_seqscan as a production solution On your advise I did not go there. On using BETWEEN:- mill2= select value from history where pointname = 'MILL2-SPEED' and dt between now() and now() - interval '5 minutes'\g value --- (0 rows) mill2= select value from history where pointname = 'MILL2-SPEED' and dt now() - interval '5 minutes'\g value - 85606.9 85606.9 85606.9 85606.9 85606.9 85606.9 etc. I have obviously used it wrong but cannot see how/why. Thanks again. Allan -Original Message- From: Tom Lane [mailto:[EMAIL PROTECTED] Sent: Tuesday, 16 November 2004 2:26 To: Michael Fuhr Cc: Harvey, Allan AC; [EMAIL PROTECTED] Subject: Re: [GENERAL] table configuration tweak for performance gain. Michael Fuhr [EMAIL PROTECTED] writes: On Mon, Nov 15, 2004 at 04:25:59PM +1100, Harvey, Allan AC wrote: I created an index on the dt column, ran ANALYSE then, EXPLAIN for some queries. The returned plan was always sequential search. Could you post a query and the EXPLAIN ANALYZE output? We could probably give better advice if we could see what's happening. Also, let's see EXPLAIN ANALYZE results after setting enable_seqscan to OFF. If that doesn't force it into an indexscan, then you have got more fundamental issues (perhaps a datatype mismatch). Note that I don't recommend turning off enable_seqscan as a production solution; but it's a useful tool for debugging. regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[GENERAL] table configuration tweak for performance gain.
Hi all, I was hoping someone might be able to set me straight so that I can get some select performance improvements. The table in question has several thousand rows currently, planning several million, it has a date time stamp column. All selects will be on ranges of the date time column. I created an index on the dt column, ran ANALYSE then, EXPLAIN for some queries. The returned plan was always sequential search. The SELECT time also indicates sequential search. Is there anything I can do to improve SELECT performance on what is essentially an ordered table? Thanks in advance. Allan ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
