Re: [GENERAL] insert binary data into a table column with psql

On Tue, Jul 24, 2012 at 7:16 PM, Tom Lane wrote: > jtkells writes: >> Thanks much for your reply, that does the trick quite nicely. But, I just >> came to the realization that this only works if your are running the >> client and the file both resides on the database server. I thought that >> I

Re: [GENERAL] insert binary data into a table column with psql

jtkells writes: > Thanks much for your reply, that does the trick quite nicely. But, I just > came to the realization that this only works if your are running the > client and the file both resides on the database server. I thought that > I would be able to do this from a remote server where

Re: [GENERAL] Smaller data types use same disk space

Adrian Klaver writes: > On 07/24/2012 03:21 PM, McGehee, Robert wrote: >> I've created two tables labeled "Big" and "Small" that both store the same >> 10 million rows of data using 493MB and 487MB of disk space respectively. >> The difference is that the "Big" table uses data types that take up

Re: [GENERAL] insert binary data into a table column with psql

On Tue, 24 Jul 2012 14:37:52 -0700, Lonni J Friedman wrote: > On Tue, Jul 24, 2012 at 2:22 PM, John R Pierce > wrote: >> On 07/24/12 1:28 PM, jkells wrote: >>> >>> from psql >>> I have tried several ways including creating a function to read a file >>> without any success but basically I want to

Re: [GENERAL] Smaller data types use same disk space

On 07/24/2012 03:21 PM, McGehee, Robert wrote: Hi, I've created two tables labeled "Big" and "Small" that both store the same 10 million rows of data using 493MB and 487MB of disk space respectively. The difference is that the "Big" table uses data types that take up more space (integer rather t

Re: [GENERAL] Smaller data types use same disk space

On 07/24/2012 03:21 PM, McGehee, Robert wrote: Hi, I've created two tables labeled "Big" and "Small" that both store the same 10 million rows of data using 493MB and 487MB of disk space respectively. The difference is that the "Big" table uses data types that take up more space (integer rather t

[GENERAL] Smaller data types use same disk space

Hi, I've created two tables labeled "Big" and "Small" that both store the same 10 million rows of data using 493MB and 487MB of disk space respectively. The difference is that the "Big" table uses data types that take up more space (integer rather than smallint, float rather than real, etc). The

Re: [GENERAL] Problem using a pl/pgsql function to populate a geometry column with x,y data - SOLVED

Nothwithstanding the fact I had my x and y's around the wrong way, I've got it working using ST_MakePoint.See below On 25/07/2012, at 12:39 AM, Mark Wynter wrote: > CREATE OR REPLACE FUNCTION load_xyz_data(sourcefile text, sourcesrid integer) > RETURNS text AS $$ > DECLARE > BEGIN > > EXEC

Re: [GENERAL] Pg 9.1: Do I need to run vacuum analyze instead vacuum full?

On Tue, Jul 24, 2012 at 5:43 PM, Anthony Bull wrote: > We found the auto vacuum would always kick in during our daily data loads in > the mornings and completely bottle neck our system, and in the end had to > turn it off and schedule our own vacuum analyze over the weekend each week > using cront

Re: [GENERAL] Problem using a pl/pgsql function to populate a geometry column with x,y data - SOLVED

Notwithstanding the fact I had my x and y's around the wrong way, I've got it working using ST_MakePoint.See below On 25/07/2012, at 12:39 AM, Mark Wynter wrote: > CREATE OR REPLACE FUNCTION load_xyz_data(sourcefile text, sourcesrid integer) > RETURNS text AS $$ > DECLARE > BEGIN > > EXECUT

Re: [GENERAL] Pg 9.1: Do I need to run vacuum analyze instead vacuum full?

We found the auto vacuum would always kick in during our daily data loads in the mornings and completely bottle neck our system, and in the end had to turn it off and schedule our own vacuum analyze over the weekend each week using crontab. We do a couple of gigs of inserts a week into our postgre

Re: [GENERAL] insert binary data into a table column with psql

On Tue, Jul 24, 2012 at 2:22 PM, John R Pierce wrote: > On 07/24/12 1:28 PM, jkells wrote: >> >> from psql >> I have tried several ways including creating a function to read a file >> without any success but basically I want to do something like the >> following from a bash shell >> >> psql -c "i

Re: [GENERAL] Pg 9.1: Do I need to run vacuum analyze instead vacuum full?

Em 24/07/2012 18:23, John R Pierce escreveu: On 07/24/12 2:15 PM, François Beausoleil wrote: Le 2012-07-24 à 16:36, Edson Richter a écrit : >I know PostgreSQL from time to time runs automatically the vacuum process. >My applications grows due lots of data import during the day (about 100Mb/da

Re: [GENERAL] Pg 9.1: Do I need to run vacuum analyze instead vacuum full?

Em 24/07/2012 18:15, François Beausoleil escreveu: Le 2012-07-24 à 16:36, Edson Richter a écrit : I know PostgreSQL from time to time runs automatically the vacuum process. My applications grows due lots of data import during the day (about 100Mb/day). Do I need also to schedule a Vacuum Analyz

Re: [GENERAL] Pg 9.1: Do I need to run vacuum analyze instead vacuum full?

Em 24/07/2012 18:15, François Beausoleil escreveu: Le 2012-07-24 à 16:36, Edson Richter a écrit : I know PostgreSQL from time to time runs automatically the vacuum process. My applications grows due lots of data import during the day (about 100Mb/day). Do I need also to schedule a Vacuum Analyz

Re: [GENERAL] Pg 9.1: Do I need to run vacuum analyze instead vacuum full?

On 07/24/12 2:15 PM, François Beausoleil wrote: Le 2012-07-24 à 16:36, Edson Richter a écrit : >I know PostgreSQL from time to time runs automatically the vacuum process. >My applications grows due lots of data import during the day (about 100Mb/day). >Do I need also to schedule a Vacuum Analyz

Re: [GENERAL] insert binary data into a table column with psql

On 07/24/12 1:28 PM, jkells wrote: from psql I have tried several ways including creating a function to read a file without any success but basically I want to do something like the following from a bash shell psql -c "insert into x (ID, load_date, image) values ($PID,clock_timestamp()::timesta

Re: [GENERAL] Pg 9.1: Do I need to run vacuum analyze instead vacuum full?

Le 2012-07-24 à 16:36, Edson Richter a écrit : > I know PostgreSQL from time to time runs automatically the vacuum process. > My applications grows due lots of data import during the day (about > 100Mb/day). > Do I need also to schedule a Vacuum Analyze to happens from time to time? I already a

[GENERAL] insert binary data into a table column with psql

I am running REDHAT 5.5 64 bit with PostgreSQL 8.4.7 64 bit. I am trying to load a binary file into a bytea column into a table without any luck from psql. On the linux server a script is looking for a file and if found would create a record into a table that contains a ID, date of load and the

[GENERAL] Pg 9.1: Do I need to run vacuum analyze instead vacuum full?

I know PostgreSQL from time to time runs automatically the vacuum process. My applications grows due lots of data import during the day (about 100Mb/day). Do I need also to schedule a Vacuum Analyze to happens from time to time? Edson -- Sent via pgsql-general mailing list (pgsql-general@postg

Re: [GENERAL] Roles with empty password (probably bug in libpq and in psql as well).

2012/7/24 Tom Lane > Dmitriy Igrishin writes: > > 2012/7/24 Tom Lane > >> Please note that empty and null are not the same thing... > > > Yes, I know. But why the ALTER ROLE treats '' as NULL and > > as the result all of values of pg_catalog.pg_authid.rolpassword are > always > > NULL even when

Re: [GENERAL] Roles with empty password (probably bug in libpq and in psql as well).

Dmitriy Igrishin writes: > 2012/7/24 Tom Lane >> Please note that empty and null are not the same thing... > Yes, I know. But why the ALTER ROLE treats '' as NULL and > as the result all of values of pg_catalog.pg_authid.rolpassword are always > NULL even when the password in ALTER ROLE was spec

[GENERAL] Problem using a pl/pgsql function to populate a geometry column with x,y data

Within a pl/pgsql function block, I'm wanting to populate a geometry column with x,y data. However when I invoke the function, I get the error message that column "y" doesn't exist, even though it does. The error message is: SELECT load_xyz_data('/var/tmp/rscp_coverage_test.txt',32754); ERROR:

Re: [GENERAL] Roles with empty password (probably bug in libpq and in psql as well).

2012/7/24 Tom Lane > Dmitriy Igrishin writes: > > But it's impossible to pass empty (NULL) password to the backend > > Please note that empty and null are not the same thing... > Yes, I know. But why the ALTER ROLE treats '' as NULL and as the result all of values of pg_catalog.pg_authid.rolpass

Re: [GENERAL] Odd corruption issue reported on dba.stackexchange.com, need advice

Scott Marlowe writes: > On Tue, Jul 24, 2012 at 7:48 AM, Craig Ringer wrote: >>> # if that still not helps, use the big hammer >>> if (-f $info{'pgdata'}.'/postmaster.pid') { >>> print "(does not shutdown, killing the process)"; >>> $pid = get_running_pid $info{'pg

Re: [GENERAL] Roles with empty password (probably bug in libpq and in psql as well).

Dmitriy Igrishin writes: > But it's impossible to pass empty (NULL) password to the backend Please note that empty and null are not the same thing... > by using libpq, because connectOptions2() defined the > fe-connect.c reads a password from the ~/.pgpass even when a password > specified as an

Re: [GENERAL] Roles with empty password (probably bug in libpq and in psql as well).

On 07/24/2012 05:41 AM, Dmitriy Igrishin wrote: Hey all, According to http://www.postgresql.org/docs/9.2/static/sql-alterrole.html A query: ALTER ROLE davide WITH PASSWORD NULL; removes a role's password. http://www.postgresql.org/docs/9.2/static/sql-createrole.html PASSWORD password Sets the

Re: [GENERAL] Odd corruption issue reported on dba.stackexchange.com, need advice

On Tue, Jul 24, 2012 at 7:48 AM, Craig Ringer wrote: > On 07/24/2012 05:31 PM, Marcin Mańk wrote: > > On Tue, Jul 24, 2012 at 3:16 AM, Craig Ringer wrote: >>> >>> It shouldn't matter - only PostgreSQL was restarted, not the whole >>> machine - and cleanly at that. Very strange. > > # if t

Re: [GENERAL] Roles with empty password (probably bug in libpq and in psql as well).

From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Dmitriy Igrishin Sent: Tuesday, July 24, 2012 10:00 AM To: Guillaume Lelarge Cc: pgsql-general@postgresql.org Subject: Re: [GENERAL] Roles with empty password (probably bug in libpq and in psql as we

Re: [GENERAL] Roles with empty password (probably bug in libpq and in psql as well).

2012/7/24 Dmitriy Igrishin > > > 2012/7/24 Guillaume Lelarge > >> On Tue, 2012-07-24 at 17:36 +0400, Dmitriy Igrishin wrote: >> > Hey Guillaume, >> > >> > 2012/7/24 Guillaume Lelarge >> > On Tue, 2012-07-24 at 16:41 +0400, Dmitriy Igrishin wrote: >> > > Hey all, >> > > >

[GENERAL] 9.1.4: pg_restore: couldn't uncompress data?

This one is concerning. Trying to restore a backup from one system to another, and got this: pg_restore: restoring data for table "userid" pg_restore: restoring data for table "values" pg_restore: [compress_io] could not uncompress data: (null) pg_restore: *** aborted because of error [lrosenman@

Re: [GENERAL] Odd corruption issue reported on dba.stackexchange.com, need advice

On 07/24/2012 05:31 PM, Marcin Mańk wrote: On Tue, Jul 24, 2012 at 3:16 AM, Craig Ringer > wrote: It shouldn't matter - only PostgreSQL was restarted, not the whole machine - and cleanly at that. Very strange. # if that still not helps, use

Re: [GENERAL] Roles with empty password (probably bug in libpq and in psql as well).

2012/7/24 Guillaume Lelarge > On Tue, 2012-07-24 at 17:36 +0400, Dmitriy Igrishin wrote: > > Hey Guillaume, > > > > 2012/7/24 Guillaume Lelarge > > On Tue, 2012-07-24 at 16:41 +0400, Dmitriy Igrishin wrote: > > > Hey all, > > > > > > According to > > http:

Re: [GENERAL] Roles with empty password (probably bug in libpq and in psql as well).

On Tue, 2012-07-24 at 17:36 +0400, Dmitriy Igrishin wrote: > Hey Guillaume, > > 2012/7/24 Guillaume Lelarge > On Tue, 2012-07-24 at 16:41 +0400, Dmitriy Igrishin wrote: > > Hey all, > > > > According to > http://www.postgresql.org/docs/9.2/static/sql-alterr

Re: [GENERAL] Roles with empty password (probably bug in libpq and in psql as well).

Hey Guillaume, 2012/7/24 Guillaume Lelarge > On Tue, 2012-07-24 at 16:41 +0400, Dmitriy Igrishin wrote: > > Hey all, > > > > According to > http://www.postgresql.org/docs/9.2/static/sql-alterrole.html > > > > A query: > > ALTER ROLE davide WITH PASSWORD NULL; > > removes a role's password. > > >

Re: [GENERAL] Roles with empty password (probably bug in libpq and in psql as well).

On Tue, 2012-07-24 at 16:41 +0400, Dmitriy Igrishin wrote: > Hey all, > > According to http://www.postgresql.org/docs/9.2/static/sql-alterrole.html > > A query: > ALTER ROLE davide WITH PASSWORD NULL; > removes a role's password. > > But it's impossible to pass empty (NULL) password to the backe

[GENERAL] Roles with empty password (probably bug in libpq and in psql as well).

Hey all, According to http://www.postgresql.org/docs/9.2/static/sql-alterrole.html A query: ALTER ROLE davide WITH PASSWORD NULL; removes a role's password. But it's impossible to pass empty (NULL) password to the backend by using libpq, because connectOptions2() defined the fe-connect.c reads a

Re: [GENERAL] how to connect to oracle database

Hello 2012/7/24 Pawel Kopka : > Hi, > > I have one question. > Is there any possibility to connect from postgresql function (plpgsql) to > oracle database with help of dblink? > > Pawel Kopka dblink from contrib allows only connect to Postgres, but you can use http://pgfoundry.org/projects/dbi-li

[GENERAL] how to connect to oracle database

Hi, I have one question. Is there any possibility to connect from postgresql function (plpgsql) to oracle database with help of dblink? Pawel Kopka

[GENERAL] can't compile twitter_fdw

Hi there, I'm trying to compile the twitter_fdw on my Ubuntu-Hardy machine, but alas... root@contactkingserver:/usr/local/pgsql/bin/twitter_fdw-1.1.1# make make -C libjson-0.8 all make[1]: Betrete Verzeichnis '/usr/local/pgsql/bin/twitter_fdw-1.1.1/libjson-0.8' sed -e 's;@PREFIX@;/usr;' -e 's;@L

Re: [GENERAL] Odd corruption issue reported on dba.stackexchange.com, need advice

On Tue, Jul 24, 2012 at 3:16 AM, Craig Ringer wrote: > It shouldn't matter - only PostgreSQL was restarted, not the whole machine >> - and cleanly at that. Very strange. > > > look at pg_ctlcluster, (which does the restart): if (!fork()) { close STDOUT; exec $pg_

[GENERAL] Re: Can't figure out how to use now() in default for tsrange column (PG 9.2)

On 2012-07-17, David Johnston wrote: > On Jul 17, 2012, at 2:32, Rafal Pietrak wrote: >> >> Is that even possible to implement? (e.g.: "SELECT * FROM log WHERE >> start_date <> '-YY-ZZ' and end_date = '-AA-BB'" - when both >> start_date and end_date possibly have 'infinity') > > I was un