Re: [GENERAL] SQL pre-processor, like ecpg, for other languages?

[Reid Thompson]

On Tue, 2015-08-11 at 07:20 -0500, John McKown wrote:

> PostgreSQL comes with ecpg which is a "pre-processor" to handle embedded EXEC
> SQL startements in C (OK, you already know that). I am wondering if anyone
> knows of any such program for other compiled languages, in particular GNU
> COBOL or ADA? (please don't shudder,
https://www.google.com/search?q=gnu+cobol+embeded+sql&ie=utf-8&oe=utf-8

5.4   Does GnuCOBOL support any SQL databases?

Yes. There are embedded SQL engines for GnuCOBOL and PostgreSQL, Oracle, and
Firebird. There has also been efforts made for accessing DB2.
http://opencobol.add1tocobol.com/gnucobol/#does-gnucobol-support-any-sql-databases

-- 
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 pre-processor, like ecpg, for other languages?

[Reid Thompson]

On Tue, 2015-08-11 at 14:32 -0400, an unknown sender wrote:

> On Tue, 2015-08-11 at 07:20 -0500, John McKown wrote:
> 

> > PostgreSQL comes with ecpg which is a "pre-processor" to handle embedded
> > EXEC
> > SQL startements in C (OK, you already know that). I am wondering if anyone
> > knows of any such program for other compiled languages, in particular GNU
> > COBOL or ADA? (please don't shudder,
> https://www.google.com/search?q=gnu+cobol+embeded+sql&ie=utf-8&oe=utf-8
> 
> 5.4   Does GnuCOBOL support any SQL databases?
> 
> Yes. There are embedded SQL engines for GnuCOBOL and PostgreSQL, Oracle, and
> Firebird. There has also been efforts made for accessing DB2.
> http://opencobol.add1tocobol.com/gnucobol/#does-gnucobol-support-any-sql
> -databases

see
http://opencobol.add1tocobol.com/gnucobol/#id974
look at the links to the right, under section 5.4
-- 
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] Processing data from table using awk.

[Reid Thompson]

On Tue, 2015-10-06 at 09:04 -0500, John McKown wrote:

> I'm wanting to do some reporting on data which I have an a PostgreSQL table.
> For lack of anything better, I've decided to see if I can do it in GNU awk.


perhaps... note the 4th extension...

https://www.gnu.org/software/gawk/manual/html_node/gawkextlib.html
As of this writing, there are seven extensions:

errno extension
GD graphics library extension
PDF extension
PostgreSQL extension
MPFR library extension (this provides access to a number of MPFR functions 
that gawk’s native MPFR support does not)
Redis extension
XML parser extension, using the Expat XML parsing librar
-- 
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] IDE for function/stored proc development.

[Reid Thompson]

On Sat, 2016-09-03 at 21:36 +1200, Tim Uckun wrote:
> Does anybody use an IDE for doing heavy duty stored proc development?  PGadmin
> is decent but I am looking for something better.
> 
> I have tried jetbrains with the db browser plugin and on the surface it seems
> like a good choice but it's really buggy when working with procs.
> 
> I also tried datagrip by jetbrains and that too seems to be all over the
> place. It has some amazing features for working with the database but some of
> the simplest stuff is lacking or half baked.
> 
> I looked at atom and could not find any useful plugins for PG.
> 
> Anybody have experience with something awesome? 

i've not yet used it much but
   dbeaverhttp://dbeaver.jkiss.org/
seems robust.

Both the Enterprise and Community edition are free.  Community edition
is open source, EE is not.

it may be worth a look
-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] PostgreSQL question re select for update (locking??)

[Reid Thompson]

Hello list,
We are using PostgreSQL 8.0.3.  Some background, and a couple of
questions..

We have a database table called "jobq" on the database machine,
and 2 networked server machines.
One of the network server machines has around 20 server processes
connecting over the network using ODBC.
These servers all attempt to "claim" jobs off the jobq to process
them.

Each server process claims a jobq record by selecting for update a
jobq record where the pid column is null, then rewrites the record with
the pid set in the pid column.

The "distilled" sql select statement is:

* SELECT J.*, C.name, C.client_id, C.priority
* FROM jobq J, campaign C
* WHERE J.pid IS NULL
* AND 'my_MMDDhhmmss'>=J.due_date
* AND J.campaign_id=C.id
* ORDER BY C.priority,J.due_date,J.item_id
* LIMIT 1 FOR UPDATE;


What we are seeing is:

- We dump hundreds of records into the jobq table, expecting that a
  server process will each claim a record (and process it), but some of
  the processes call the claim function but get nothing returned by the
  select; they sleep for a short time (eg 10 seconds - this sleep
  differs on a per process basis) then attempt to claim again.
  Sometimes this claim returns a jobq record, other times it may take 4
  or 5 attempted claims before a record is actually returned by the
  select.

  Our expectation was that with hundreds of selectable records available,
  that each server process would 'get' the next available record -- 
  Is this a false expectation on our part?
  Is the currently 'active' select for update limit 1 blocking the others?
  If not, can someone give us some direction..??

- We do not see any SQL error in our server process diagnostic output
  (but I will revisit this to double check).

- We do not see any SQL error in the postgres logs.


Here is an example / snapshot of postgres processes on the database machine:
(ps -ef |grep "postgres.*192" |grep -v idle)

postgres 27866 6434 0 05:05 ? 00:03:21 postgres: obc obc 192.168.100.164(34499) 
SELECT
postgres 27871 6434 0 05:08 ? 00:05:57 postgres: obc obc 192.168.100.171(37066) 
SELECT waiting
postgres 27887 6434 0 05:09 ? 00:01:44 postgres: obc obc 192.168.100.171(37130) 
SELECT



Here is an example / snapshot of sessions:

 datid | procpid | usesysid |current_query | query_start
---+-+--+--+-
 17231 |   23540 |  100 |  |
 17231 |   23541 |  100 |  |
 17231 |   23542 |  100 |  |
 17231 |   23543 |  100 |  |
 17231 |   23544 |  100 |  |
 17231 |   23545 |  100 |  |
 17231 |   23546 |  100 |  |
 17231 |   23547 |  100 |  |
 17231 |   23548 |  100 |  |
 17231 |   23549 |  100 |  |
 17231 |   23550 |  100 |  |
 17231 |   23551 |  100 |  |
 17231 |   23552 |  100 |  |
 17231 |   23553 |  100 |  |
 17231 |   23554 |  100 |  |
 17231 |   23555 |  100 |  |
 17231 |   23556 |  100 |  |
 17231 |   23557 |  100 |  |
 17231 |   23558 |  100 |  |
 17231 |   23559 |  100 |  |
 17231 |   23560 |  100 |  |
 17231 |   23561 |  100 |  |
 17231 |   23562 |  100 |  |
 17231 |   23563 |  100 |  |
 17231 |   23564 |  100 |  |
 17231 |   23565 |  100 |  |
 17231 |   23566 |  100 |  |
 17231 |   23567 |  100 |  |
 17231 |   23568 |  100 |  |
 17231 |   23569 |  100 |  |
 17231 |   23570 |  100 |  |
 17231 |   23571 |  100 |  |
 17231 |   23572 |  100 |  |
 17231 |   23573 |  100 |  |
 17231 |   23574 |  100 |  |
 17231 |   23575 |  100 |  |
(36 rows)



Here is an example / snapshot of pg_locks:

 relation | database | transaction |  pid  |   mode   | granted
--+--+-+---+--+-
18384 |17231 | | 23544 | AccessShareLock  | t
18267 |17231 | | 23556 | AccessShareLock  | t
18267 |17231 | | 23556 | RowShareLock | t
  |  |32900999 | 23564 | ShareLock| f
  |  |32900999 | 23556 | ExclusiveLock| t
  |  |32901005 | 23568 | ExclusiveLock| t
18418 |17231 | | 23556 | AccessShareLock  | t
18384 |17231 | | 23556 | AccessShareLock  | t
18357 |17231 | | 23555 | AccessShareLock  | t
18256 |17231 | | 23544 | AccessShareLock  | t
18352 |17231 | | 23554 | AccessShareLock  | t
18352 |17231 | | 23554 | RowShareLock | t

Re: [GENERAL] free scheduled import utility

[Reid Thompson]

On Tue, 2007-07-10 at 14:32 +0200, Zlatko Matic wrote:
> Hello.
> Is there any free program/utility for batch imports from .csv files, that 
> can be easily scheduled for daily inserts of data to PostgreSQL tables?
> Regards,
> 
> Zlatko 
> 
> 
> ---(end of broadcast)---
> TIP 5: don't forget to increase your free space map settings

cron, psql and http://www.postgresql.org/docs/8.2/static/sql-copy.html

---(end of broadcast)---
TIP 4: Have you searched our list archives?

   http://archives.postgresql.org/

Re: [GENERAL] Help with date math

[Reid Thompson]

Chris Hoover wrote:
I need some help.  I am trying to replicate a function from Sybase 
ASA, and am having difficulty.


I need to be able to subtract 2 date (or timestamps) and return the 
results expressed in days, weeks, month, quarters, or years.  How do I 
do this?


I believe Postgres is returning the number of days when you subtract 
to days.

i.e. postgres=# select current_date - '2007/01/01';
 ?column?
--
  200
(1 row)



However, I can not figure out how to get the results expressed as 7 
months, or 2 quarters, x weeks, or 0 years.  I have tried to use 
date_part, but since the subtraction is returning an integer, it is 
not happy.


Any help would be greatly appreciated.

Chris

P.S.

I'm trying to replicate Sybase's datediff function.

postgres=# select age(current_date,'2007/01/01');
 age

6 mons 20 days
(1 row)


http://www.postgresql.org/docs/8.2/static/functions-datetime.html

---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
  choose an index scan if your joining column's datatypes do not
  match

Re: [GENERAL] Help with date math

[Reid Thompson]

Chris Hoover wrote:
I need some help.  I am trying to replicate a function from Sybase 
ASA, and am having difficulty.


I need to be able to subtract 2 date (or timestamps) and return the 
results expressed in days, weeks, month, quarters, or years.  How do I 
do this?


I believe Postgres is returning the number of days when you subtract 
to days.

i.e. postgres=# select current_date - '2007/01/01';
 ?column?
--
  200
(1 row)



However, I can not figure out how to get the results expressed as 7 
months, or 2 quarters, x weeks, or 0 years.  I have tried to use 
date_part, but since the subtraction is returning an integer, it is 
not happy.


Any help would be greatly appreciated.

Chris

P.S.

I'm trying to replicate Sybase's datediff function.

postgres=# select date_part('month', age(current_date, '2007/01/01'));
date_part
---
6
(1 row)


---(end of broadcast)---
TIP 6: explain analyze is your friend

Re: [GENERAL] Linux distro

[Reid Thompson]

On Wed, 2007-08-01 at 13:29 +0200, [EMAIL PROTECTED] wrote:
> Hello,
> 
> I bought a Dell server and I am going to use it for installing PostgrSQL
> 8.2.4. I always used Windows so far and I would like now to install a
> Linux distribution on the new server. Any suggestion on which distribution
> ? Fedora, Ubuntu server, Suse or others?
> 
> Thanks in advance,
> Paolo Saudin
> 
> 
> 
> ---(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

Is this going to be a production server. or a learning server, or a i'm
learning all things linux server/desktop?

If it's a dedicated production server, look at UBUNTU 6.10 server.
If you're planning to connect a monitor and run X-windows ( i.e. I
bought a server, but i'm going to use it as a learning platform for
LINUX in general also), i'd suggest either UBUNTU 6.10 or 7.04 desktop
( or, start with the 6.10 server, and use apt/synaptic/etc to add
whatever additional packages you want )

---(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] Linux distro

[Reid Thompson]

Andrej Ricnik-Bay wrote:

On 8/2/07, Reid Thompson <[EMAIL PROTECTED]> wrote:

  

If it's a dedicated production server, look at UBUNTU 6.10 server.
If you're planning to connect a monitor and run X-windows ( i.e. I
bought a server, but i'm going to use it as a learning platform for
LINUX in general also), i'd suggest either UBUNTU 6.10 or 7.04 desktop
( or, start with the 6.10 server, and use apt/synaptic/etc to add
whatever additional packages you want )


No offense, but *if* you have to suggest Ubuntu you should be
suggesting 6.06 LTS,
oops, pulled the distro number from memory, 6.06 LTS for server is the 
one I meant rather than 6.10



 not any of the bleating, errrh, bleeding edge
and quickly fluctuating versions.  In a production server environment
people commonly aren't eager to update the whole OS every year. I
certainly wouldn't.

And if you settle for a stable server environment, I, too, would
rather go with the original debian than with ubuntu.  Not that
I particularly like either of them :}



Cheers,
Andrej

---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
  



---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings

Re: [GENERAL] reporting tools

[Reid Thompson]

On Wed, 2007-08-22 at 18:57 -0400, Geoffrey wrote:
> We are looking for a reporting tool that will enable users to generate 
> their own reports.  Something like Crystal Reports.
> 
> Anyone using something like this with Postgresql?
> 

agata, datavision, jasper reports, birt, openRPT -- google shows
numerous results

---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster

Re: [GENERAL] Cannot Start Postgres After System Boot

[Reid Thompson]

On 10/20/2010 6:53 PM, Rich Shepard wrote:

  For reasons I do not understand, the Slackware start-up file for postgres
(/etc/rc.d/rc.postgresql) fails to work properly after I reboot the system.
(Reboots normally occur only after a kernel upgrade or with a hardware
failure that crashes the system.)

  Trying to restart the system manually (su postgres -c 'postgres -D
/var/lib/pgsql/data &') regardless of the presence of /tmp/.s.PGSQL.5432
and /var/lib/pgsql/postmaster.pid. Here's what I see:

[rshep...@salmo ~]$ su postgres -c 'postgres -D /var/lib/pgsql/data &'
Password: [rshep...@salmo ~]$ LOG:  could not bind IPv4 socket: Address already 
in use
HINT:  Is another postmaster already running on port 5432? If not, wait a
few seconds and retry.
WARNING:  could not create listen socket for "localhost"
FATAL:  could not create any TCP/IP sockets

  If someone would be kind enough to point out what I'm doing incorrectly
(e.g., removing /tmp/.s.PGSQL.5432 and postmaster.pid when the startup
process complains they're not right) I'll save this information for the next
time. I can also provide the 'start' section of the Slackware init file so I
could learn why it's not working properly.

TIA,

Rich


what does
$ netstat -an|grep 5432
return?

what does
$ ps -ef|grep post
return?

The above indicates that the tcp ipv4 socket is already bound by some process

--
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] Cannot Start Postgres After System Boot

[Reid Thompson]

On Thu, 2010-10-21 at 10:35 -0700, Rich Shepard wrote:
> On Thu, 21 Oct 2010, Scott Marlowe wrote:
> 
> > So, what do
> >
> > telnet localhost 5432
> 
> Scott,
> 
>That port's clear:
> 
> [rshep...@salmo ~]$ telnet localhost 5432
> Trying 127.0.0.1...
> Connected to localhost.
> Escape character is '^]'.
> 
> > AND
> > psql -h localhost -l
> 
>Huh!
> 
> [rshep...@salmo ~]$ psql -h localhost -l
>   List of databases
> Name|   Owner| Encoding 
> ---++--
>   aesi  | sql-ledger | LATIN1
>   cms   | rshepard   | UTF8
>   postgres  | postgres   | UTF8
>   refdb | postgres   | UTF8
>   scirefs   | rshepard   | LATIN1
>   template0 | postgres   | UTF8
>   template1 | postgres   | UTF8
> (7 rows)
> 
>So, why can't I connect to a database by entering, for example, 'psql
> aesi'?
> 
> Thanks,
> 
> Rich

what does
$ netstat -an |grep 5432
return?

something is running on tcp port 5432

Re: [GENERAL] Cannot Start Postgres After System Boot

[Reid Thompson]

On Thu, 2010-10-21 at 11:38 -0700, Rich Shepard wrote:
> On Thu, 21 Oct 2010, Reid Thompson wrote:
> 
> > what does
> > $ netstat -an |grep 5432
> > return?
> >
> > something is running on tcp port 5432
> 
>Doesn't show that.
> 
> [rshep...@salmo ~]$ netstat -an |grep 5432
> tcp0  0 127.0.0.1:5432  0.0.0.0:*   LISTEN

The above line means that something is listening on TCP port 5432.
You do NOT have a listener on unix socket port 5432.
EX:  my box has both

$ netstat -an|grep 5432
tcp0  0 0.0.0.0:54320.0.0.0:*   LISTEN 
unix  2  [ ACC ] STREAM LISTENING 413260   
/var/run/postgresql/.s.PGSQL.5432

If I telnet to 
$ telnet localhost 5432

and run
$ netstat -an|grep 5432
tcp0  0 0.0.0.0:54320.0.0.0:*   LISTEN 
tcp0  0 127.0.0.1:56771 127.0.0.1:5432  ESTABLISHED
tcp0  0 127.0.0.1:5432  127.0.0.1:56771 ESTABLISHED
unix  2  [ ACC ] STREAM LISTENING 413260   
/var/run/postgresql/.s.PGSQL.5432
rthom...@raker>~ 

the established connection is shown
and lsof shows
$ lsof -i TCP:5432
COMMAND   PID USER   FD   TYPE DEVICE SIZE/OFF NODE NAME
telnet  22648 rthompso3u  IPv4 445992  0t0  TCP 
raker.ateb.com:56771->raker.ateb.com:postgresql (ESTABLISHED)
rthom...@raker>~  
$ 

Re: [GENERAL] Cannot Start Postgres After System Boot

[Reid Thompson]

On Thu, 2010-10-21 at 11:45 -0700, Rich Shepard wrote:
> On Thu, 21 Oct 2010, Scott Marlowe wrote:
> 
> > So a postgres IS running on your machine.  I put it to you it's not
> > running where you think it is.
> 
>When I run 'ps ax | grep post' I found a few postgres processes. I tried
> '/etc/rc.d/rc.postgresql stop' but that had no effect. I killed the lowest
> numbered process and that removed them all. However, I still cannot start a
> new postgresql process.
> 
> Rich
> 
> -- 
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general



What does
$ su - postgres
$ pg_ctl -D /var/lib/pgsql/data
$ ps -ef|grep post

return

Re: [GENERAL] Full Text Search - Slow on common words

[Reid Thompson]

On Thu, 2010-10-28 at 12:08 -0700, sub3 wrote:
> Hi,
> 
> I have a small web page set up to search within my domain based on keywords. 
> One of the queries is:
>   SELECT page.id ts_rank_cd('{1.0, 1.0, 1.0, 1.0}',contFTI,q) FROM page, 
> to_tsquery('steve') as q WHERE contFTI @@ q 
> 
> My problem is: when someone puts in a commonly seen word, the system slows
> down and takes a while because of the large amount of data being returned
> (retrieved from the table) & processed by the rand_cd function.
> 
> How does everyone else handle something like this?  I can only think of 2
> possible solutions:
> - change the query to search for the same terms at least twice in the same
> document (can I do that?)
> - limit any searches to x results before ranking & tell the user their
> search criteria is too generic.
> 
> Is there a better solution that I am missing?
> 

if the keyword is that common, is it really a keyword?  Exclude it.

[GENERAL] Group by and limit

[Reid Thompson]

Reposting as I noticed that the original was in reply to a different subject.


Hey Folks – have a coded myself into a corner yet?

I have a situation with a select count / group by / order by query that I need to limit each group to 500 
entries. Not seeing a way to do this in a single query, do I need to use multiple queries?

Group x has about 200 entries in it; group y has about 5-8k per x.

select x, y, count(*) as counter from mytable
group by x, y
order by x, counter, y


I only want the first 500 for each x.

Any tips or tricks someone might know would be appreciated.

I’m using postgres 8.3.7.



--
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] Tuning Postgres for single user manipulating large amounts of data

[Reid Thompson]

On 12/09/2010 09:59 AM, Andy Colson wrote:
> On 12/9/2010 8:50 AM, Andy Colson wrote:
>> On 12/9/2010 6:25 AM, Paul Taylor wrote:

>> You need to bump up your SHMMAX is your OS.
> 
> sorry: SHMMAX _in_ your OS.
> 
> its an OS setting not a PG one.
> 
> -Andy
> 
> 
scroll down to the section on OSX
http://developer.postgresql.org/pgdocs/postgres/kernel-resources.html

-- 
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] Tuning Postgres for single user manipulating large amounts of data

[Reid Thompson]

On 12/09/2010 12:36 PM, Paul Taylor wrote:
> On 09/12/2010 15:12, Reid Thompson wrote:
>> On 12/09/2010 09:59 AM, Andy Colson wrote:
>>> On 12/9/2010 8:50 AM, Andy Colson wrote:
>>>> On 12/9/2010 6:25 AM, Paul Taylor wrote:
>>>> You need to bump up your SHMMAX is your OS.
>>> sorry: SHMMAX _in_ your OS.
>>>
>>> its an OS setting not a PG one.
>>>
>>> -Andy
>>>
>>>
>> scroll down to the section on OSX
>> http://developer.postgresql.org/pgdocs/postgres/kernel-resources.html
>>
> Thanks guys, but one think I dont get is why does setting shared_buffers to 
> 40mb break the kernel limit, I mean 40 mb doesnt sound
> like very much at all
> 
> Paul

It's not -- from the same page (near the top)
17.4.1. Shared Memory and Semaphores

Shared memory and semaphores are collectively referred to as "System V IPC" 
(together with message queues, which are not relevant
for PostgreSQL). Almost all modern operating systems provide these features, 
but many of them don't have them turned on or
sufficiently sized by default, especially as available RAM and the demands of 
database applications grow.

and/but most of these system defaults originated when system RAM availability 
was much smaller

-- 
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] Tuning Postgres for single user manipulating large amounts of data

[Reid Thompson]

On Thu, 2010-12-09 at 17:40 +, Paul Taylor wrote:
> what 
> have I got to be careful of. 

I think that was in reference to turning fsync off, not work_mem values.

[GENERAL] work_mem = 900MB but Sort Method: external merge Disk: 304008kB

[Reid Thompson]

What am I missing that causes this to resort to sorting on disk?

obc=# select version();
 version
--
 PostgreSQL 8.3.7 on x86_64-redhat-linux-gnu, compiled by GCC gcc (GCC) 4.1.2 
20071124 (Red Hat 4.1.2-42)
(1 row)

Time: 43.920 ms

> ---
>  Limit  (cost=1063641.92..1063643.47 rows=20 width=13) (actual 
> time=422710.147..422711.328 rows=20 loops=1)
>->  Unique  (cost=1063641.92..1064133.33 rows=6320 width=13) (actual 
> time=422710.022..422711.127 rows=20 loops=1)
>  ->  Sort  (cost=1063641.92..1063887.62 rows=98282 width=13) (actual 
> time=422710.014..422710.696 rows=172 loops=1)
>Sort Key: cpn.value
>Sort Method:  external merge  Disk: 304008kB
>->  Nested Loop  (cost=647.20..1061026.67 rows=98282 width=13) 
> (actual time=61.029..71867.921 rows=9627373 loops=1)
>  ->  HashAggregate  (cost=647.20..648.15 rows=95 width=4) 
> (actual time=60.950..64.350 rows=596 loops=1)
>->  Hash Join  (cost=4.59..646.96 rows=95 width=4) 
> (actual time=0.352..57.210 rows=596 loops=1)
>  Hash Cond: (cb.client_id = c.id)
>  ->  Seq Scan on contact_block cb  
> (cost=0.00..596.31 rows=12031 width=8) (actual time=0.015..26.757 rows=10323 
> loops=1)
>  ->  Hash  (cost=4.58..4.58 rows=1 width=4) 
> (actual time=0.064..0.064 rows=1 loops=1)
>->  Seq Scan on clients c  
> (cost=0.00..4.58 rows=1 width=4) (actual time=0.021..0.055 rows=1 loops=1)
>  Filter: ((name)::text = 'Kmart 
> Pharmacies, Inc.'::text)
>  ->  Index Scan using extra_import_param_blk_item_tag on 
> extra_import_param cpn  (cost=0.00..11039.67 rows=9777 width=17) (actual 
> time=0.057..61.769 rows=16153 loops=596)
>Index Cond: ((cpn.block_id = cb.id) AND 
> ((cpn.tag)::text = 'PATNAME'::text))
>  Total runtime: 422920.026 ms
> (16 rows)
> 
> Time: 422924.289 ms
> obc=# show sort_mem;
>  work_mem 
> --
>  900MB
> (1 row)



-- 
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] work_mem = 900MB but Sort Method: external merge Disk: 304008kB

[Reid Thompson]

On 02/22/2011 12:06 PM, Tom Lane wrote:
> Reid Thompson  writes:
>> What am I missing that causes this to resort to sorting on disk?
> 
> The in-memory space required to sort N tuples can be significantly
> larger than the on-disk space, because the latter representation is
> optimized to be small and the in-memory representation not so much.
> I haven't seen a 3X differential before, but it's not outside the realm
> of reason, especially for narrow rows like these where it's all about
> the overhead.  I suspect if you crank work_mem up still more, you'll see
> it switch over.  It flips to on-disk sort when the in-memory
> representation exceeds the limit ...
> 
>   regards, tom lane

ahh, ok; the underlying cpn.value table is 11 GB so I understand how even 
slightly less optimized representation could be
significantly larger than ~300MB/900MB

Thanks,
reid

-- 
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] select to_timestamp('02/26/2011 14:50', 'MM/DD/YYYY HH24MI')

[Reid Thompson]

On 02/25/2011 09:30 AM, akp geek wrote:

Hi all -

I have recently upgraded from 8.3 to 9.0.2.

when I run the following sql in 9.0.2 "select to_timestamp('02/26/2011 14:50', 
'MM/DD/ HH24MI') " I am getting the following
error  and the sql runs fine in older version

Is it my installation issue or library missing?


ERROR:  invalid value ":5" for "MI"
DETAIL:  Value must be an integer.

** Error **

ERROR: invalid value ":5" for "MI"
SQL state: 22007
Detail: Value must be an integer.



Thanks for the help
Regards


military time doesn't have the ':' in the HHMM.
look closely at your 'working' result, does it look like this?


reporting=# select to_timestamp('02/26/2011 14:50', 'MM/DD/ HH24MI');
  to_timestamp

 2011-02-26 14:00:00-05
(1 row)

reporting=# select to_timestamp('02/26/2011 1450', 'MM/DD/ HH24MI');
  to_timestamp

 2011-02-26 14:50:00-05
(1 row)

Note the invalid time for the first result.

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] orphaned?? tmp files

[Reid Thompson]

Given the below information, is it reasonable to assume that files in pgsql_tmp 
dated prior to 10 days ago can be safely removed?

postgres@hw-prod-repdb1> uptime
 15:01:35 up 10 days, 13:50,  3 users,  load average: 1.23, 1.15, 0.63
postgres@hw-prod-repdb1> pwd
/mnt/iscsi/psql_tmp/tmpdata
postgres@hw-prod-repdb1> ls -rtlh *
-rw--- 1 postgres postgres4 May 11  2009 PG_VERSION

pgsql_tmp:
total 0

41099:
total 3.0G
-rw--- 1 postgres postgres 8.0K Mar 12  2010 88326
-rw--- 1 postgres postgres0 Mar 12  2010 88324
-rw--- 1 postgres postgres0 Mar 16  2010 88580
-rw--- 1 postgres postgres0 Mar 16  2010 88577.3
-rw--- 1 postgres postgres0 Mar 16  2010 88577.4
-rw--- 1 postgres postgres0 Mar 16  2010 88577.5
-rw--- 1 postgres postgres 8.0K Mar 16  2010 88592
-rw--- 1 postgres postgres 8.0K Jun  2  2010 94968
-rw--- 1 postgres postgres0 Jun  2  2010 94966
-rw--- 1 postgres postgres 8.0K Jun  2  2010 94974
-rw--- 1 postgres postgres0 Jun  2  2010 94972
-rw--- 1 postgres postgres 8.0K Aug 26  2010 104559
-rw--- 1 postgres postgres0 Aug 26  2010 104557
-rw--- 1 postgres postgres 1.0G Oct 27 10:05 88577
-rw--- 1 postgres postgres 1.0G Oct 27 10:07 88577.1
-rw--- 1 postgres postgres 824M Oct 27 10:09 88577.2
-rw--- 1 postgres postgres 488K Nov  2 12:17 111724
-rw--- 1 postgres postgres  43M Dec  7 14:46 94963
-rw--- 1 postgres postgres  25M Dec  7 14:46 94969
-rw--- 1 postgres postgres 8.0K Feb 22 15:45 104560
-rw--- 1 postgres postgres 8.0K Feb 22 15:45 104554
-rw--- 1 postgres postgres  72M Mar  2 16:12 88321

--
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] Web Hosting

[Reid Thompson]

On 3/5/2011 4:08 PM, matty jones wrote:
I already have a domain name but I am looking for a hosting company that I can use PG with.  The few I have 
contacted have said that they support MySQL only and won't give me access to install what I need or they 
want way to much.  I don't need a dedicated host which so far seems the only way this will work, all the 
companies I have researched so far that offer shared hosting or virtual hosting only use MySQL.  I will take 
care of the setup and everything myself but I have already written my code using PG/PHP and I have no 
intention of switching.


Thanks.

http://hub.org/


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Re: Remote PostgreSQL database - C/C++ program / Unix / Required Libraries

[Reid Thompson]

On Sun, 2014-11-30 at 12:51 -0700, Léa Massiot wrote:
> Me again. Sorry.
> 
> This looks good too: "Building libpq Programs":
> http://www.postgresql.org/docs/9.1/static/libpq-build.html
> 
> But what do I need to install on B?
> 
> Best regards.
> 
> 
> 
> --
> View this message in context: 
> http://postgresql.nabble.com/Remote-PostgreSQL-database-C-C-program-Unix-Required-Libraries-tp5828704p5828705.html
> Sent from the PostgreSQL - general mailing list archive at Nabble.com.
> 
> 
> 


http://www.postgresql.org/docs/9.1/static/libpq-example.html

-- 
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] bloating vacuum

[Reid Thompson]

On Tue, 2013-05-14 at 14:51 +, S H wrote:
> I am doing some experiment to understand the behaviour of manual
> vacuum.
> 
> I created small table and started doing insertion/deletion/updation on
> 2 rows in infinite loop. It started bloating around 844 times, but
> after it stopped bloating.. what could be the reason?
> 
> In between i am running manual vacuum analyze ( without full option)

Explanation is described here
 http://www.postgresql.org/docs/9.1/static/sql-vacuum.html




-- 
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] Developer GUI tools for PostgreSQL

[Reid Thompson]

See  dbeaver. http://dbeaver.jkiss.org 

community and Enterprise editions are free and it's very good

-- 
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] Auto incrementing primary keys

[Reid Thompson]

On Mon, 2008-02-18 at 17:46 +, Raymond O'Donnell wrote:
> On 18/02/2008 13:14, pgsql_user wrote:
> > so wouldnt I run out of ids one day, if there are lot of failed insert
> > statements, lets say for every successful insert there are 50
> > unsuccessful inserts, so ids would be 1, 50, 100, and once I have
> > thousands of rows, I will run out of IDs ? should I use bigserial
> > instead ?
> 
> Well, that depends on your usage, so only you can answer that. According 
> to the docs, "serial" creates an integer column,  which will give you 
> 2147483647 values - how quickly will you use that lot up? If you think 
> you will run out, by all means use bigserial.
> 
> Ray.
> 
> ---
> Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland
> [EMAIL PROTECTED]
> ---
> 
> ---(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

http://www.google.com/search?q=2147483647+seconds+to
+years&ie=utf-8&oe=utf-8&aq=t&rls=com.ubuntu:en-US:official&client=firefox-a

at one per second.
2 147 483 647 seconds = 68.0511039 years


---(end of broadcast)---
TIP 6: explain analyze is your friend

Re: [GENERAL] loading a psql input file on win xp

[Reid Thompson]

akshay bhat wrote:

HELLO
i have psql file which is to be used for loading a database

it was downloaded from this link http://conceptnet.media.mit.edu/
the description says
The ConceptNet 3 database, as a PostgreSQL input file. You will need to 
be running a PostgreSQL server to install ConceptNet 3.

i was earlier
suggested to use following command
to add it to database

psql -d  -f conceptnet-2007-09-25.psql

however i do not know where to execute this command
i am using a windows xp with with postgresql installed in

D:\Program Files\PostgreSQL\8.3\


the file is located in folder

D:\conc\

now can someone please tell me where to execute above command
should i use dos (i mean command prompt )

please help sorry for my foolish question

--
akshay uday bhat.
t.y.c.e.
department of chemical engineering
university institute of chemical technology
mumbai India

On action alone be thy interest,
Never on its fruits.
Let not the fruits of action be thy motive,
Nor be thy attachment to inaction.

Ask and it shall be given to you; seek and you shall find; knock and it 
shall be opened to you


you'll also probably have to add the parameter -h 127.0.0.1

--
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] loading a psql input file on win xp

[Reid Thompson]

On Sun, 2008-03-09 at 19:41 +, Raymond O'Donnell wrote:
> On 09/03/2008 15:34, Reid Thompson wrote:
> 
> > you'll also probably have to add the parameter -h 127.0.0.1
> 
> psql defaults to connecting to the local machine unless you tell it 
> otherwise, so this is redundant; though it certainly won't do any harm 
> either.
> 
Not redundent.  Unless something has changed, psql's default connection
method is via unix socket 5432 and this socket does not exist on
windows. The -h is required to have psql connect to the network socket
which is available on windows.


-- 
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] loading a psql input file on win xp

[Reid Thompson]

On Mon, 2008-03-10 at 06:55 +, Raymond O'Donnell wrote:
> On 09/03/2008 22:33, Reid Thompson wrote:
> > On Sun, 2008-03-09 at 19:41 +, Raymond O'Donnell wrote:
> >> On 09/03/2008 15:34, Reid Thompson wrote:
> >>
> >>> you'll also probably have to add the parameter -h 127.0.0.1
> >> psql defaults to connecting to the local machine unless you tell it 
> >> otherwise, so this is redundant; though it certainly won't do any harm 
> >> either.
> >>
> > Not redundent.  Unless something has changed, psql's default connection
> > method is via unix socket 5432 and this socket does not exist on
> > windows. The -h is required to have psql connect to the network socket
> > which is available on windows.
> 
> Oh? Well, on my XP laptop, the following -
> 
> psql -U postgres
> 
> - connects without problems to the "postgres" database. Of course, there 
> *is* the following line in pg_hba.conf -
> 
>hostall all127.0.0.1/32  md5
> 
> - which was there from installation time.
> 
> Ray.
> 
> ---
> Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland
> [EMAIL PROTECTED]
> ---
Cool.  I'll have to check the pg_hba.conf on my windows system and see
what it has in it.  My windows box is running either 8.0.x or 8.1.x.
Has this setting always been this way for the windows installer?

-- 
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] Does anyone know of a job scheduler that uses PostgreSQL?

[Reid Thompson]

On Wed, 2009-07-01 at 16:16 -0500, Anthony Caduto wrote:
> I am looking for something similar to:
> http://www.arcanadev.com/adtempus/  but without the MS SQL server 
> dependency.
> 
> Thanks,
> 
> Tony
> 

http://www.opensymphony.com/quartz/  might fit the bill

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] iGen with PostgreSQL 8.4 on Sun Fire X4140

[Reid Thompson]

may be of interest -- 
http://blogs.sun.com/jkshah/entry/igen_with_postgresql_8_4


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Partitioned table question

[Reid Thompson]

Assuming the examples on
http://www.postgresql.org/docs/8.3/static/ddl-partitioning.html

where measurement has children as noted

CREATE TABLE measurement (
city_id int not null,
logdate date not null,
peaktempint,
unitsales   int
);

CREATE TABLE measurement_y2006m02 ( ) INHERITS (measurement);
CREATE TABLE measurement_y2006m03 ( ) INHERITS (measurement);
...
CREATE TABLE measurement_y2007m11 ( ) INHERITS (measurement);
CREATE TABLE measurement_y2007m12 ( ) INHERITS (measurement);
CREATE TABLE measurement_y2008m01 ( ) INHERITS (measurement);

and insertion is governed ala the trigger example

CREATE TRIGGER insert_measurement_trigger
BEFORE INSERT ON measurement
FOR EACH ROW EXECUTE PROCEDURE measurement_insert_trigger();

CREATE OR REPLACE FUNCTION measurement_insert_trigger()
RETURNS TRIGGER AS $$
BEGIN
IF ( NEW.logdate >= DATE '2006-02-01' AND NEW.logdate < DATE 
'2006-03-01' ) THEN
INSERT INTO measurement_y2006m02 VALUES (NEW.*);
ELSIF ( NEW.logdate >= DATE '2006-03-01' AND NEW.logdate < DATE 
'2006-04-01' ) THEN
INSERT INTO measurement_y2006m03 VALUES (NEW.*);
...
ELSIF ( NEW.logdate >= DATE '2008-01-01' AND NEW.logdate < DATE 
'2008-02-01' ) THEN
INSERT INTO measurement_y2008m01 VALUES (NEW.*);
ELSE
RAISE EXCEPTION 'Date out of range.  Fix the 
measurement_insert_trigger() function!';
END IF;
RETURN NULL;
END;
$$
LANGUAGE plpgsql;


If I know that no incoming data will be going into measurement_y2007m11
and I NO INHERIT measurement_y2007m11 from measurement, do I have to
immediately update the function measurement_insert_trigger() to remove
references to measurement_y2007m11, or will the function continue to
work fine and I can update it when convenient?

Thanks,
reid

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] computed values in plpgsql

[Reid Thompson]

We have a set of tables that we're partitioning by year and month - 
e.g. payments_parent, partitioned into payments_200901, payments200902, ...
and  inquiries_parent, partitioned into inquiries_200901, inquiries_200902, ...

Each table has a timestamp field import_ts that can be used to partition
the data by month. 
The example trigger procs have an IF statement for *each* month that has
a partition - growing as time goes by, so you get some long trigger
procs if you have incoming data over a range


IF import_ts::date >= DATE '2009-01-01' and import_ts::date <= DATE 
'2009-01-31' THEN
INSERT INTO payments_200901 VALUES(NEW.*)
ELSIF import_ts::date >= DATE '2009-02-01' and import_ts::date <= 
DATE '2009-02-28' THEN
INSERT INTO payments_200902 VALUES(NEW.*)
...


Ditto for each other _parent/partition series.
It would be much simpler to compute the table name from the timestamp,
and re-use the proc for both payments and inquiries tables:



CREATE OR REPLACE FUNCTION partition_ins_trigger( ) 
RETURNS TRIGGER AS
$$
DECLARE
insStmt  text;
tableName   text;
tableDate   text;
BEGIN
 tableDate := to_char(NEW.import_ts, '_MM');
 tableName := replace( TG_RELNAME, '_parent', tableDate );
-- Either
 INSERT INTO tableNAme VALUES(NEW.*)
-- OR
 EXECUTE 'INSERT INTO ' || tableName || ' VALUES( ' || NEW.* || ')';
 RETURN NULL;   
   
END;
 
$$ language 'plpgsql' volatile;
 
CREATE TRIGGER payments_partition_ins_trigger BEFORE INSERT ON payments_parent 
   FOR EACH ROW EXECUTE PROCEDURE partition_ins_trigger();
 
CREATE TRIGGER inquiries_partition_ins_trigger BEFORE INSERT ON 
inquiries_parent 
   FOR EACH ROW EXECUTE PROCEDURE partition_ins_trigger();
--


The problem is that I can't use a computed table name in a plpgsql
INSERT, and I haven't found a way to use the NEW.* values in an EXECUTE
statement. Is there a way to do this, to prevent the long series of IF's
in an INSERT trigger proc?


-- 
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] computed values in plpgsql

[Reid Thompson]

On Mon, 2009-09-28 at 12:42 -0400, Merlin Moncure wrote:

> the best way to do this is very version dependent.  the basic trick is
> to use text cast to pass a composite type into the query sting.
> 
> one way:
> execute 'insert into foo_something select (' || new::text || '::foo).*';
> 
> you can try:
> execute 'insert into foo_something select ($1::foo).*' using new::text;
> 
> merlin

thanks,  we're using version 8.3.7.

-- 
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] computed values in plpgsql

[Reid Thompson]

On Mon, 2009-09-28 at 11:05 -0400, Reid Thompson wrote:
> We have a set of tables that we're partitioning by year and month - 

> 

We can't seem to quite get it right...
This is our quick stub test.

--
-- Tables:
--

CREATE TABLE payments (
 id serial,
 payment_name varchar(32),
 payment_type varchar(10),
 when_done timestamp,
 amount numeric(12,3));


CREATE TABLE payments_200901 
   (CHECK (when_done::date >= DATE '2009-01-01' and when_done::date <=
'2009-01-31' ) ) 
   inherits (payments);

CREATE TABLE payments_200902
   (CHECK (when_done::date >= DATE '2009-02-01' and when_done::date <=
'2009-02-28' ) ) 
   inherits (payments);

CREATE TABLE payments_200903
   (CHECK (when_done::date >= DATE '2009-03-01' and when_done::date <=
'2009-03-31' ) ) 
   inherits (payments);

--
-- Trigger proc:
---

CREATE OR REPLACE FUNCTION partition_ins_trigger( ) 
RETURNS TRIGGER AS
$$
DECLARE
insStmt  text;
tableName   text;
tableDate   text;
BEGIN
 tableDate := to_char(NEW.when_done, '_MM');
 tableName := TG_RELNAME || tableDate;
 execute 'insert into ' || tableName || ' select (' || new::text ||
')::' || TG_RELNAME || ').*';
  RETURN NULL;

END;
$$ language 'plpgsql' volatile;


--
-- Trigger
--

CREATE TRIGGER payments_partition_ins_trigger BEFORE INSERT ON payments
   FOR EACH ROW EXECUTE PROCEDURE partition_ins_trigger();

--
-- Insert
--

# insert into payments(payment_name, payment_type, when_done, amount) 
   values('FRED','WIDGET', TIMESTAMP '2009-01-15 14:20:00', 14.50 );

--
-- Error
--

LINE 1: ... ((7,FRED,WIDGET,"2009-01-15 14:20:00",14.500))::payments).*
^
QUERY:  insert into payments_200901 select ((7,FRED,WIDGET,"2009-01-15
14:20:00",14.500))::payments).*
CONTEXT:  PL/pgSQL function "partition_ins_trigger" line 8 at EXECUTE
statement



-- If I remove the .* from the function, I get


# insert into payments(payment_name, payment_type, when_done, amount)
values('FRED','WIDGET','2009-01-15 14:20:00', 14.50 );

ERROR:  column "fred" does not exist
LINE 1: insert into payments select (3,FRED,WIDGET,"2009-01-15 14:20...
   ^
QUERY:  insert into payments select (3,FRED,WIDGET,"2009-01-15
14:20:00",14.500)::payments
CONTEXT:  PL/pgSQL function "partition_ins_trigger" line 8 at EXECUTE
statement


-

So the ::text is converting NEW, but what it converts into doesn't fly
in the EXECUTE's INSERT


-- 
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] computed values in plpgsql

[Reid Thompson]

On Tue, 2009-09-29 at 07:54 -0400, Merlin Moncure wrote:

> you are missing some quotes in there.  also, don't use 'values', use
> select.  see my example above:
> execute 'insert into foo_something select (''' || new::text || '''::foo).*';
> 
> the actual query should look like:
> insert into payments(payment_name, payment_type, when_done, amount)
>   select ('(7,FRED,WIDGET,"2009-01-15 14:20:00",14.500)'::payments).*;
> 
> merlin

Merlin,
thank you.  That appears to work except for one case. If one of the
string literals in the insert happens to have an escaped quote (e.g.
'Joe''s Crabshack') the insert falls over due to quoting.

insert into inquiries(who, when_done, question ) values('FRED',TIMESTAMP 
'2009-01-16 09:14:00', 'Where''s my money');

LINE 1: ...901 select ('(1,FRED,"2009-01-16 09:14:00","Where's my money...
 ^
QUERY:  insert into inquiries_200901 select ('(1,FRED,"2009-01-16 
09:14:00","Where's my money")'::inquiries).*

Does anyone know...
  if 8.4 would have the same issue?
  is there a non-trivial solution to this that could be implemented in the 
plpgsql function
  

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] what is the best way to concat fields that may contain null as if they were empty strings

[Reid Thompson]

In the case where a_text is null, I essentially want the same result as the 
case when a_text = ''.

would this:
  select a_int || coalesce(a_text,'') from test1 where a_int = 102;
be the proper way?


postgres=# \d test1
 Table "public.test1"
 Column |Type |   Modifiers 
  
+-+---
 a_int  | integer | not null default 
nextval('test1_a_int_seq'::regclass)
 a_text | character varying(200)  | 
 dt | timestamp without time zone | default now()
Indexes:
"test1_pkey" PRIMARY KEY, btree (a_int)

postgres=# select count(*) from test1;
  count  
-
 100
(1 row)

postgres=# insert into test1(a_text) values('');
INSERT 0 1
postgres=# select max(a_int) from test1;
   max   
-
 101
(1 row)

postgres=# select a_int || a_text from test1 where a_int = 101;
 ?column? 
--
 101
(1 row)

postgres=# insert into test1(a_text) values(null);
INSERT 0 1
postgres=# select a_int || a_text from test1 where a_int = 102;
 ?column? 
--
 
(1 row)

postgres=# select * from test1 where a_int >= 101;
  a_int  | a_text | dt 
-++
 101 || 2009-10-09 11:54:38.46
 102 || 2009-10-09 11:56:00.37607
(2 rows)





-- 
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] Partitioned table question

[Reid Thompson]

So we know have data in ~30 partitioned tables.
Our requirements now necessitate adding some columns to all these tables
( done ) which will get populated via batch sql for the older tables and
by normal processing as we move forward.

The batch update is going to result in dead tuples in the older tables.
What would be the recommended way to recover this dead space?
Vacuum full children tables + reindex children tables?  or

Thanks,
reid



-- 
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] Junk date getting uploaded into date field

[Reid Thompson]

On Tue, 2013-11-05 at 04:14 -0800, bsreejithin wrote:

> Not able to post the attached details as a comment in the reply box, so
> attaching it as an image file :
> 
>
>
>
> --
> View this message in context: 
> http://postgresql.1045698.n5.nabble.com/Junk-date-getting-uploaded-into-date-field-tp5776969p5776987.html
> Sent from the PostgreSQL - general mailing list archive at Nabble.com.
>
>

to achieve what you want bdate needs to be defined as a date, not a
string.

test=# create table temptabl(bdate date);
CREATE TABLE
Time: 239.358 ms
test=# insert into temptabl values('33-OCT-2013');
ERROR:  date/time field value out of range: "33-OCT-2013"
LINE 1: insert into temptabl values('33-OCT-2013');
^
HINT:  Perhaps you need a different "datestyle" setting.
Time: 0.288 ms
test=#

Re: [GENERAL] Postgresql CBT

[Reid Thompson]

On 05/24/2011 10:49 AM, Scott Marlowe wrote:

Oh good.  My first response from google, with safe search turned off,
was much more distressing...


in other news, google trends for cbt just jumped

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Postgresql CBT

[Reid Thompson]

On 05/24/2011 01:02 PM, fork wrote:



A psql prompt and the excellent postgresql documentation?


if you're concerned about mucking something up, download a postgresql livecd

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Can I safely remove what appear to be obsolete temp files

[Reid Thompson]

My postgresql instance was last started on Apr21.
In my temp space directory, I have various files that I believe are orphaned.
Given the information below, can I safely rm the files in 
/mnt/iscsi/psql_tmp/tmpdata/41099 that are older than Jun 22?
If yes, should these files have been cleaned up by the restart on Apr21?
If yes, if I restart the DB today/in the next few days and the files from Jun 
22 remain after the restart, can I safely rm them?

thanks,
reid

reporting=# \db
 List of tablespaces
Name|  Owner   |  Location
+--+-
 indexspace | postgres | /mnt/iscsi/psql_idx/idxdata
 pg_default | postgres |
 pg_global  | postgres |
 tempspace  | postgres | /mnt/iscsi/psql_tmp/tmpdata


postgres@hw-prod-repdb1> ps -ef|grep postg |grep -v idle
postgres 19895 1  0 Apr21 ?00:05:19 /usr/bin/postmaster -p 5432 -D 
/mnt/iscsi/psql/data
postgres 19903 19895  0 Apr21 ?00:00:10 postgres: logger process
postgres 19906 19895  0 Apr21 ?00:10:34 postgres: writer process
postgres 19907 19895  0 Apr21 ?00:02:04 postgres: wal writer process
postgres 19908 19895  0 Apr21 ?00:08:42 postgres: stats collector 
process
root 21228 16899  0 08:08 pts/400:00:00 su - postgres
postgres 21230 21228  0 08:08 pts/400:00:00 -bash
postgres 24485 21230  0 10:02 pts/400:00:00 ps -ef
postgres 24486 21230  0 10:02 pts/400:00:00 grep postg

postgres@hw-prod-repdb1> pwd
/mnt/iscsi/psql_tmp/tmpdata/41099

postgres@hw-prod-repdb1> ls -rlt
total 3044944
-rw--- 1 postgres postgres   8192 Mar 12  2010 88326
-rw--- 1 postgres postgres  0 Mar 12  2010 88324
-rw--- 1 postgres postgres  0 Mar 16  2010 88580
-rw--- 1 postgres postgres  0 Mar 16  2010 88577.3
-rw--- 1 postgres postgres  0 Mar 16  2010 88577.4
-rw--- 1 postgres postgres  0 Mar 16  2010 88577.5
-rw--- 1 postgres postgres   8192 Mar 16  2010 88592
-rw--- 1 postgres postgres   8192 Jun  2  2010 94968
-rw--- 1 postgres postgres  0 Jun  2  2010 94966
-rw--- 1 postgres postgres   8192 Jun  2  2010 94974
-rw--- 1 postgres postgres  0 Jun  2  2010 94972
-rw--- 1 postgres postgres   8192 Aug 26  2010 104559
-rw--- 1 postgres postgres  0 Aug 26  2010 104557
-rw--- 1 postgres postgres 1073741824 Oct 27  2010 88577
-rw--- 1 postgres postgres 1073741824 Oct 27  2010 88577.1
-rw--- 1 postgres postgres  862986240 Oct 27  2010 88577.2
-rw--- 1 postgres postgres 499712 Nov  2  2010 111724
-rw--- 1 postgres postgres   44523520 Dec  7  2010 94963
-rw--- 1 postgres postgres   25657344 Dec  7  2010 94969
-rw--- 1 postgres postgres   33628160 Jun 22 18:46 88321
-rw--- 1 postgres postgres  16384 Jun 22 19:49 104560
-rw--- 1 postgres postgres  16384 Jun 22 19:49 104554

--
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] Relative path specified for data_directory is not working as expected

[Reid Thompson]

On 06/23/2011 11:08 AM, Eduard-Cristian Stefan wrote:

 D:\me\usr\PostgreSQL\bin/pg_ctl.exe runservice -N "pgsql" -D 
"d:/me/etc/PostgreSQL"
not very familiar with the windows version, but I think -D is supposed to point to the data directory which in your later info is 
listed as

data_directory = 'd:/me/data/PostgreSQL'

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] to_timestamp() and timestamp without time zone

[Reid Thompson]

On Thu, 2011-06-23 at 13:26 -0700, Adrian Klaver wrote:

> On 06/23/2011 01:07 PM, Steve Crawford wrote:
> > On 06/23/2011 12:30 PM, hernan gonzalez wrote:
> >>
> >>
> >> On Thu, Jun 23, 2011 at 4:15 PM, Adrian Klaver
> >> mailto:adrian.kla...@gmail.com>> wrote:
> >>
> >> On 06/23/2011 11:40 AM, hernan gonzalez wrote:
> >>
> >> Rather than being not viable, I'd argue that is is not correct.
> >> Rather, a simple direct cast will suffice:
> >> '2011-12-30 00:30:00'::timestamp without time zone
> >>
> >>
> >> That works only for that particular format. The point is that, for
> >> example, if I have some local date time
> >> stored as a string in other format ('30/12/2011 00:30:00') I
> >> cannot
> >> reliably parse it as a TIMESTAMP. Which I should.
> >>
> >>
> >> Works here. I am in US PDT:
> >>
> >> select to_timestamp('30/12/2011 00:30:00', 'DD/MM/ HH24:MI:SS
> >> ')::timestamp with time zone;
> >>
> >> to_timestamp
> >> 
> >> 2011-12-30 00:30:00-08
> >>
> >>
> >> My point is to parse a TIMESTAMP WITHOUT TIME ZONE - and that that
> >> should NOT depend on the server/session TIMEZONE.
> >>
> >> Try this:
> >>
> >> # set TIMEZONE='XXX8';
> >> # select to_timestamp('2007-12-30 00:30:00','-MM-DD
> >> HH24:MI:SS')::timestamp;
> >> 2007-12-30 00:30:00
> >> # set TIMEZONE='America/Argentina/Buenos_Aires';
> >> select to_timestamp('2007-12-30 00:30:00','-MM-DD
> >> HH24:MI:SS')::timestamp;
> >> 2007-12-30 01:30:00
> > ...snip...
> >
> > Every example here starts, at its core, with to_timestamp. That function
> > returns a timestamp *with* time zone so of-course the current timezone
> > setting will influence it. Stop using it - it doesn't do what you want.
> >
> > If you cast directly to a timestamp *without* time zone you can take
> > advantage of the many formats PostgreSQL supports.
> >
> > See:
> > http://www.postgresql.org/docs/9.0/interactive/datatype-datetime.html#DATATYPE-DATETIME-DATE-TABLE
> > for supported formats. Note also that you can use "set datestyle" to
> > match your MDY or DMY date formatting.
> >
> > If the format you require is so obscure that PostgreSQL can't handle it
> > out-of-the-box (and the one you have presented is completely vanilla),
> > use the many string-handling functions to alter your input as necessary.
> 
> Possibly:
> test=> select (to_date('30/12/2007','DD/MM/') + 
> '00:30'::time)::timestamp;
>timestamp
> -
>   2007-12-30 00:30:00
> (1 row)
> 
> 
> >
> > Cheers,
> > Steve
> >
> 
> 
> -- 
> Adrian Klaver
> adrian.kla...@gmail.com
> 


test=# SET datestyle to DMY;
SET
test=# select '30/12/2011 00:30:00'::timestamp;
  timestamp  
-
 2011-12-30 00:30:00
(1 row)

test=# 

Re: [GENERAL] Contrib source

[Reid Thompson]

On Thu, 2011-06-30 at 10:46 -0400, gnuo...@rcn.com wrote:
>  I'll need the contrib source, and I don't find it anywhere.  I must not be 
> looking in the right place.
> 
> Where might that be?


http://www.postgresql.org/ftp/source/


-- 
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] interesting finding on order by behaviour

[Reid Thompson]

On Fri, 2011-07-22 at 10:11 -0700, Samuel Hwang wrote:

> I ran the same tests in SQL Server 2008R2, Oracle10 and PostgreSQL
> 9.0.4 and found something interesting...
> results
> =
> SQL Server 2008 R2 (with case insensitive data, the ordering follows
> ASCII order)
> 
> Oracle 10 (data is case-sensitive, the ordering follows ASCII order)
> 
> PostgreSQL 9.0.4 (data is case-sensitive, the ordering is ...
> DIFFERENT)
> 
> 


perhaps   http://www.postgresql.org/docs/9.1/static/charset.html  will
provide an answer

[GENERAL] Re: Need help with what I think is likely a simple query - for each distinct val, return only one record with the most recent date.

[Reid Thompson]

On 9/12/2011 9:54 PM, Reid Thompson wrote:

Ack -- i flubbed the subject and sample.
The sample data should be

val  val2date
11   2011-01-01
22   2011-01-02
33   2011-01-03
41   2011-01-04
52   2011-01-05
53   2011-01-01
41   2011-01-02
62   2011-01-03
43   2011-01-04
31   2011-01-05
22   2011-01-06
43   2011-01-07
61   2011-01-08
42   2011-01-09
53   2011-01-01
21   2011-01-02
42   2011-01-03
23   2011-01-04
11   2011-01-01
22   2011-01-02
33   2011-01-03
41   2011-01-04
32   2011-01-05
13   2011-01-01
21   2011-01-02
32   2011-01-03
43   2011-01-04
54   2012-01-01

resultset:

13   2011-01-01
22   2011-01-06
31   2011-01-05
42   2011-01-09
52   2011-01-05
61   2011-01-08

where any one of these 3

11   2011-01-01
11   2011-01-01
13   2011-01-01

or any one of these 2
31   2011-01-05
32   2011-01-05

are suitable for val = 1, val = 3 respectively.




sigh -- looks like I fat fingered one of my resultset values above.
But, I think this gives me what I want:

test=# select distinct on (val1) val1, val2, val3 from (SELECT  
max(val3) OVER (PARTITION BY val1), * FROM sampledata) as sq where val3 
= max order by val1;

 val1 | val2 |val3
--+--+
 1| 3| 2011-01-01
 2| 2| 2011-01-06
 3| 2| 2011-01-05
 4| 2| 2011-01-09
 5| 4| 2012-01-01
 6| 1| 2011-01-08
(6 rows)



 val1 | val2 |val3
--+--+
 1| 3| 2011-01-01
 1| 1| 2011-01-01
 1| 1| 2011-01-01
 2| 2| 2011-01-02
 2| 1| 2011-01-02
 2| 3| 2011-01-04
 2| 1| 2011-01-02
 2| 2| 2011-01-06
 2| 2| 2011-01-02
 3| 3| 2011-01-03
 3| 3| 2011-01-03
 3| 2| 2011-01-05
 3| 1| 2011-01-05
 3| 2| 2011-01-03
 4| 1| 2011-01-04
 4| 1| 2011-01-02
 4| 3| 2011-01-04
 4| 3| 2011-01-07
 4| 3| 2011-01-04
 4| 2| 2011-01-09
 4| 1| 2011-01-04
 4| 2| 2011-01-03
 5| 4| 2012-01-01
 5| 2| 2011-01-05
 5| 3| 2011-01-01
 5| 3| 2011-01-01
 6| 1| 2011-01-08
 6| 2| 2011-01-03
(28 rows)


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Re: Need help with what I think is likely a simple query - for each distinct val, return only one record with the most recent date.

[Reid Thompson]

Ack -- i flubbed the subject and sample.
The sample data should be

val  val2date
11   2011-01-01
22   2011-01-02
33   2011-01-03
41   2011-01-04
52   2011-01-05
53   2011-01-01
41   2011-01-02
62   2011-01-03
43   2011-01-04
31   2011-01-05
22   2011-01-06
43   2011-01-07
61   2011-01-08
42   2011-01-09
53   2011-01-01
21   2011-01-02
42   2011-01-03
23   2011-01-04
11   2011-01-01
22   2011-01-02
33   2011-01-03
41   2011-01-04
32   2011-01-05
13   2011-01-01
21   2011-01-02
32   2011-01-03
43   2011-01-04
54   2012-01-01

resultset:

13   2011-01-01
22   2011-01-06
31   2011-01-05
42   2011-01-09
52   2011-01-05
61   2011-01-08

where any one of these 3

11   2011-01-01
11   2011-01-01
13   2011-01-01

or any one of these 2
31   2011-01-05
32   2011-01-05

are suitable for val = 1, val = 3 respectively.





On 9/12/2011 8:54 PM, Reid Thompson wrote:

Could someone point me in the right direction..
Thanks - reid

Given the example data,
how do I write a query that will give me the resultset:

12011-01-01
22011-01-06
32011-01-05
42011-01-09
52011-01-05
62011-01-08

I.E. for each distinct val, return the record with the most recent date.

ex data
val  date
12011-01-01
22011-01-02
32011-01-03
42011-01-04
52011-01-05
52011-01-01
42011-01-02
62011-01-03
42011-01-04
32011-01-05
22011-01-06
42011-01-07
62011-01-08
42011-01-09
52011-01-01
22011-01-02
42011-01-03
22011-01-04
12011-01-01
22011-01-02
32011-01-03
42011-01-04
32011-01-05
12011-01-01
22011-01-02
32011-01-03
42011-01-04
52011-01-01
---

$ cat sampledata|sort -k1,2
12011-01-01
12011-01-01
12011-01-01
22011-01-02
22011-01-02
22011-01-02
22011-01-02
22011-01-04
22011-01-06
32011-01-03
32011-01-03
32011-01-03
32011-01-05
32011-01-05
42011-01-02
42011-01-03
42011-01-04
42011-01-04
42011-01-04
42011-01-04
42011-01-07
42011-01-09
52011-01-01
52011-01-01
52011-01-01
52011-01-05
62011-01-03
62011-01-08





--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Need help with what I think is likely a simple query - for each distinct val, return only the record with the most recent date.

[Reid Thompson]

Could someone point me in the right direction..
Thanks - reid

Given the example data,
how do I write a query that will give me the resultset:

12011-01-01
22011-01-06
32011-01-05
42011-01-09
52011-01-05
62011-01-08

I.E. for each distinct val, return the record with the most recent date.

ex data
val  date
12011-01-01
22011-01-02
32011-01-03
42011-01-04
52011-01-05
52011-01-01
42011-01-02
62011-01-03
42011-01-04
32011-01-05
22011-01-06
42011-01-07
62011-01-08
42011-01-09
52011-01-01
22011-01-02
42011-01-03
22011-01-04
12011-01-01
22011-01-02
32011-01-03
42011-01-04
32011-01-05
12011-01-01
22011-01-02
32011-01-03
42011-01-04
52011-01-01
---

$ cat sampledata|sort -k1,2
12011-01-01
12011-01-01
12011-01-01
22011-01-02
22011-01-02
22011-01-02
22011-01-02
22011-01-04
22011-01-06
32011-01-03
32011-01-03
32011-01-03
32011-01-05
32011-01-05
42011-01-02
42011-01-03
42011-01-04
42011-01-04
42011-01-04
42011-01-04
42011-01-07
42011-01-09
52011-01-01
52011-01-01
52011-01-01
52011-01-05
62011-01-03
62011-01-08



--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Re: Need help with what I think is likely a simple query - for each distinct val, return only one record with the most recent date.

[Reid Thompson]

On Tue, 2011-09-13 at 16:39 +0200, Hannes Erven wrote:
> Reid,
> 
> 
> > where any one of these 3
> >
> > 11   2011-01-01
> > 11   2011-01-01
> > 13   2011-01-01
> >
> > or any one of these 2
> > 31   2011-01-05
> > 32   2011-01-05
> >
> > are suitable for val = 1, val = 3 respectively.
> 
> 
> Can you please describe in words what you are trying to accomplish? When
> I look at your data and expected output, I'd say you want this:
> 
>For each distinct value of "val1", return any value of "val2" and
>the lowest value of "date".

for each distinct value of "val1', return the highest value(most recent)
of "date" and the value of "val2" associated with that date




-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Is libpq version indifferent? I.E. can 8.3.x compiled binaries run without issue against a 9.1 backend, or are clients required to be re-compiled against the 9.1 libpq

[Reid Thompson]

I believe that there should be no issue, but am asking to be sure.

Thanks,
reid

--
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] Solaris 10u9, PG 8.4.6, 'c' lang function, fails on 1 of 5 servers

[Reid Thompson]

On Thu, 2011-09-29 at 12:08 -0500, dennis jenkins wrote:
> ERROR:  could not load library "/db/pgsql_micr_parser_64.so": ld.so.1:
> postgres: fatal: /db/pgsql_micr_parser_64.so: Permission denied

for a different shared object, but may provide clues...

Error: "- adding iplike database function... 
org.postgresql.util.PSQLException: ERROR: could not access file
'/lib/iplike.so': Permission denied"
The PostgreSQL server cannot access the iplike.so file. This could be
due to the file itself not having appropriate permissions for the user
that PostgreSQL runs as and

/or one or more of the parent directories of the iplike.so not having
appropriate permissions.



Error: "- adding iplike database function... 
org.postgresql.util.PSQLException: ERROR: could not load library ..."
The latter part of the error could be something like "/iplike.so:
cannot open shared object file: No such file or directory" or "ld.so.1:
postgres: fatal: /iplike.so: wrong ELF class: ELFCLASS32".

The PostgreSQL server cannot load the iplike.so file. This is almost
always caused by the PostgreSQL server and the iplike.so file being
compiled for different processor instruction sets.



-- 
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] list of databases in C ? libpq ?

[Reid Thompson]

On 5/7/2010 1:48 PM, Alex Hunsaker wrote:

On Fri, May 7, 2010 at 11:18, Joao Ferreira gmail
  wrote:

Hello all,

I need to write an application in C to read the list of databases
currently in the server. very much like a "psql -l"...



The first example in the online docs does exactly that...
http://www.postgresql.org/docs/8.1/static/libpq-example.html

$ cc -I /usr/include/postgresql-8.4/ -lpq libpq.c -o libpqex

$ ./libpqex dbname=test
datnamedatdba encoding   datcollate datctype   datistemplate  datallowconn 
datconnlimit   datlastsysoid  datfrozenxid   dattablespace  datconfig  datacl


template1  10 6  en_US.UTF-8en_US.UTF-8t  t  -1 
  11563  6481663  {=c/postgres,postgres=CTc/postgres}
template0  10 6  en_US.UTF-8en_US.UTF-8t  f  -1 
  11563  6481663  {=c/postgres,postgres=CTc/postgres}
postgres   10 6  en_US.UTF-8en_US.UTF-8f  t  -1 
  11563  6481663
test   16384  6  en_US.UTF-8en_US.UTF-8f  t  -1 
  11563  6481663
sigcap 16438  0  en_US.UTF-8en_US.UTF-8f  t  -1 
  11563  6481663



--
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] Disk space occupied by a table in postgresql

[Reid Thompson]

On Sat, 2008-08-09 at 04:59 -0400, Fouad Zaryouh wrote:
> Hi Aravind,
> 
> Run the following query
> 
> SELECT relname, reltuples, relpages * 8 / 1024 AS "MB" FROM pg_class
> ORDER BY relpages DESC;
> 
> 
> 
> relname = table name
> relpages = size in MB
> reltuples = number of rows.
> 
> Hope this help.
> 
> 
> 
> Fouad Zaryouh
> 
> http://www.flipcore.com
> 
> 
> 
> 
> On Sat, Aug 9, 2008 at 3:18 AM, aravind chandu
> <[EMAIL PROTECTED]> wrote:
> Hello,
> 
> I installed postgresql on linux system, I
> create a table and inserted a large data into the table what I
> would like to know is how to calculate the disk space occupied
> by the table .Is there any procedure to find it out or simply
> a command .Please give me some suggestion.
> 
> 
> Thank You,
> Avin.
> 
> 
> 
> 

This may be of use in recent versions...
select pg_size_pretty(pg_database_size('table_name'))

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Regression failing on build -> ERROR: could not access file "$libdir/plpgsql": No such file or directory

[Reid Thompson]

I'm getting this failure on compilation.  Could someone point me in the 
direction of a fix?

Thanks,
reid

System:  AIX 5.3
$ uname -a
AIX aix53-dev-1 3 5 000B357F4C00

Configuration params:
./configure --prefix=/usr/local/pgsql833 --without-readline --disable-shared

The Error:
...SNIP...
/usr/bin/gmake -C test/regress all
gmake[2]: Entering directory `/home/rthompso/postgresql-8.3.3/src/test/regress'
/usr/bin/gmake -C ../../../src/port all
gmake[3]: Entering directory `/home/rthompso/postgresql-8.3.3/src/port'
gmake[3]: Nothing to be done for `all'.
gmake[3]: Leaving directory `/home/rthompso/postgresql-8.3.3/src/port'
gmake[2]: stat:regress.so: There are too many levels of symbolic links to 
translate a path name.
rm -f regress.so
ln -s  regress.so
gmake[2]: stat:regress.so: There are too many levels of symbolic links to 
translate a path name.
/usr/bin/gmake -C ../../../contrib/spi refint.so autoinc.so
gmake[3]: Entering directory `/home/rthompso/postgresql-8.3.3/contrib/spi'
gmake[3]: `refint.so' is up to date.
gmake[3]: `autoinc.so' is up to date.
gmake[3]: Leaving directory `/home/rthompso/postgresql-8.3.3/contrib/spi'
rm -rf ./testtablespace
mkdir ./testtablespace
gmake[2]: Leaving directory `/home/rthompso/postgresql-8.3.3/src/test/regress'
gmake[1]: Leaving directory `/home/rthompso/postgresql-8.3.3/src'
/usr/bin/gmake -C config all
gmake[1]: Entering directory `/home/rthompso/postgresql-8.3.3/config'
gmake[1]: Nothing to be done for `all'.
gmake[1]: Leaving directory `/home/rthompso/postgresql-8.3.3/config'
All of PostgreSQL successfully made. Ready to install.
/usr/bin/gmake -C src/test check
gmake[1]: Entering directory `/home/rthompso/postgresql-8.3.3/src/test'
/usr/bin/gmake -C regress check
gmake[2]: Entering directory `/home/rthompso/postgresql-8.3.3/src/test/regress'
/usr/bin/gmake -C ../../../src/port all
gmake[3]: Entering directory `/home/rthompso/postgresql-8.3.3/src/port'
gmake[3]: Nothing to be done for `all'.
gmake[3]: Leaving directory `/home/rthompso/postgresql-8.3.3/src/port'
gmake[2]: stat:regress.so: There are too many levels of symbolic links to 
translate a path name.
rm -f regress.so
ln -s  regress.so
gmake[2]: stat:regress.so: There are too many levels of symbolic links to 
translate a path name.
/usr/bin/gmake -C ../../../contrib/spi refint.so autoinc.so
gmake[3]: Entering directory `/home/rthompso/postgresql-8.3.3/contrib/spi'
gmake[3]: `refint.so' is up to date.
gmake[3]: `autoinc.so' is up to date.
gmake[3]: Leaving directory `/home/rthompso/postgresql-8.3.3/contrib/spi'
rm -rf ./testtablespace
mkdir ./testtablespace
./pg_regress --temp-install=./tmp_check --top-builddir=../../.. 
--srcdir=/home/rthompso/postgresql-8.3.3/src/test/regress --temp-port=55432 
--schedule=./parallel_schedule --multibyte=SQL_ASCII --load-language=plpgsql   
== removing existing temp installation==
== creating temporary installation==
== initializing database system   ==
== starting postmaster==
running on port 55432 with pid 618520
== creating database "regression" ==
CREATE DATABASE
ALTER DATABASE
== installing plpgsql ==
ERROR:  could not access file "$libdir/plpgsql": No such file or directory
command failed: 
"/home/rthompso/postgresql-8.3.3/src/test/regress/./tmp_check/install//usr/local/pgsql833/bin/psql"
 -X -c "CREATE LANGUAGE \"plpgsql\"" "regression"
server stopped
gmake[2]: *** [check] Error 2
gmake[2]: Leaving directory `/home/rthompso/postgresql-8.3.3/src/test/regress'
gmake[1]: *** [check] Error 2
gmake[1]: Leaving directory `/home/rthompso/postgresql-8.3.3/src/test'
gmake: *** [check] Error 2
make: 1254-004 The error code from the last command is 2.


Stop.


the lib is getting built, but appears that it is not getting installed as part 
of the regression test
$ pwd 
/home/rthompso/postgresql-8.3.3

[rthompso@ ]/home/rthompso/postgresql-8.3.3
$ find . -name \*plpgsql\*
./doc/src/sgml/plpgsql.sgml
./src/pl/plpgsql
./src/pl/plpgsql/src/plpgsql.h
./src/pl/plpgsql/src/libplpgsql.a
./src/test/regress/expected/plpgsql.out
./src/test/regress/sql/plpgsql.sql
./src/test/regress/tmp_check/install/usr/local/pgsql833/doc/html/plpgsql.html
./src/test/regress/tmp_check/install/usr/local/pgsql833/doc/html/plpgsql-overview.html
./src/test/regress/tmp_check/install/usr/local/pgsql833/doc/html/plpgsql-structure.html
./src/test/regress/tmp_check/install/usr/local/pgsql833/doc/html/plpgsql-declarations.html
./src/test/regress/tmp_check/install/usr/local/pgsql833/doc/html/plpgsql-expressions.html
./src/test/regress/tmp_check/install/usr/local/pgsql833/doc/html/plpgsql-statements.html
./src/test/regress/tmp_check/install/usr/local/pgsql833/doc/html/plpgsql-control-structures.html
./src/test/regress/tmp_check/install/usr/local/pgsql833/doc/html/plpgsql-cursors.html
./src/test/regress/tmp_ch

Re: [GENERAL] Regression failing on build -> ERROR: could not access file "$libdir/plpgsql": No such file or directory

[Reid Thompson]

On Fri, 2008-08-15 at 11:03 -0400, Tom Lane wrote:
> Reid Thompson Woulwrites:
> > I'm getting this failure on compilation.  Could someone point me in the 
> > direction of a fix?
> 
> You probably ought to inquire into the cause of these:
> > gmake[2]: stat:regress.so: There are too many levels of symbolic links to 
> > translate a path name.
> 
> Usually that indicates circular symlinking :-(.  You didn't show us the
> part of the log about building plpgsql.so, but I'll bet it had the same
> issue, whatever it is.
> 
>   regards, tom lane

Would plpgsql.so get built with..

Configuration params:
./configure --prefix=/usr/local/pgsql833 --without-readline --disable-shared


I'll see if I can figure out the symbolic links recursion issue.

thanks,
reid


-- 
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] Regression failing on build -> ERROR: could not access file "$libdir/plpgsql": No such file or directory

[Reid Thompson]

On Fri, 2008-08-15 at 16:37 -0400, Tom Lane wrote:
> Reid Thompson <[EMAIL PROTECTED]> writes:
> > Would plpgsql.so get built with..
> 
> > ./configure --prefix=/usr/local/pgsql833 --without-readline --disable-shared
>   
> 
> Uh, no.  That probably explains why regress.so didn't get built, either.
> 
>   regards, tom lane

OK -- rebuilt without '--disable-shared', and 
===
 All 114 tests passed. 
===

1) Should the Docs note that '--disable-shared' will prevent the compile
time regression checks from running
2) Should the compilation note to the user that '--disable-shared' will
prevent compile time regression checks from running
3) should the error message output when regression fails for the above
reason not be along the lines of
...snip...
gmake[2]: stat:regress.so: There are too many levels of symbolic links 
to translate a path name.
rm -f regress.so
ln -s  regress.so
gmake[2]: stat:regress.so: There are too many levels of symbolic links 
to translate a path name.
...snip...
4) if '--disable-shared' is passed, should the user be notified that
compile time regression checks will be skipped ( and then skip them ).

-- 
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] Cannot connect to server

[Reid Thompson]

On Fri, 2008-09-05 at 11:44 +0200, Filip Rembiałkowski wrote:

> 
> is the server running (see process list, ps ax | grep postgres)
> does the socket file exist? (see filesystem, ls -l /tmp/.s.PGSQL.5432)
Since you say things work for several days, then stop -- make sure
there's no braindead automated cleanup of /tmp that is
removing /tmp/.s.PGSQL.5432
> 
> it's possible that the client tries to connect via non-existing
> socket. it happens when server config is different from compiled-in
> defaults for socket location.
> 
> in this case try to connect via TCP,  this should help

-- 
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] Piping CSV data to psql when executing COPY .. FROM STDIN

[Reid Thompson]

Allan Kamau wrote:
Sam, I have been unable to understand your shell script well enough to 
use it. Seems am slow this afternoon :-)


On this list I saw a message detailing using copy as illustrated below 
(see )when I run this command I get the following output (see 
)




COPY abc FROM STDIN WITH CSV HEADER;
\.
1  qrsta
2   zvyb


As you can see the ./ is placed a the top instead of the bottom of the 
output. The does create some error when I run this output via psql.
I then get a datatype error when I pass to psql the following (edited) 
sql from a text editor (see )



COPY abc FROM STDIN WITH CSV HEADER;
1   qrsta
2   zvyb
\.


The error reads as follows

psql:sql/some2.sql:7: ERROR:  invalid input syntax for integer: "1   
qrsta"

CONTEXT:  COPY item_major, line 1, column id: "1   qrsta"





\echo 'COPY abc FROM STDIN WITH CSV HEADER;'
COPY
(
SELECT * FROM abc
)
to STDOUT
WITH delimiter E'\t'
\echo '\\.'





Sam Mason wrote:

On Tue, Oct 28, 2008 at 01:25:00PM +0200, Allan Kamau wrote:
 
The alternative I am attempting is to use "COPY abc FROM STDIN WITH 
HEADER". I pipe the contents of the CSV file on my PC to the psql 
command (that connects to the remote PC) while issuing this copy 
command.

This does seems not to work.



It does whenever I try it and if you've ever restored from a pg_dump
then you've used it as well!

 

Is there a way around it.



When I've had a CSV file and needed to bung it into a database, I've
tended to end up with shell scripts like this before:

  ( echo 'COPY abc FROM STDIN WITH CSV HEADER;'
cat "$1"
echo '\.'
  ) | psql

an alternative is to use the "\copy" feature inside psql that does this
sort of thing internally.  One thing to be aware of is that it doesn't
expect a semicolon at the end of the line, but is otherwise the same as
the SQL COPY command.


  Sam

  




are these space delimited values, or tab delimited values?
1   qrsta
2   zvyb

--
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] Piping CSV data to psql when executing COPY .. FROM STDIN

[Reid Thompson]

Allan Kamau wrote:

Reid Thompson wrote:

Allan Kamau wrote:
Sam, I have been unable to understand your shell script well enough 
to use it. Seems am slow this afternoon :-)


On this list I saw a message detailing using copy as illustrated 
below (see )when I run this command I get the following output 
(see )




COPY abc FROM STDIN WITH CSV HEADER;
\.
1  qrsta
2   zvyb


As you can see the ./ is placed a the top instead of the bottom of 
the output. The does create some error when I run this output via psql.
I then get a datatype error when I pass to psql the following 
(edited) sql from a text editor (see )



COPY abc FROM STDIN WITH CSV HEADER;
1   qrsta
2   zvyb
\.


The error reads as follows

psql:sql/some2.sql:7: ERROR:  invalid input syntax for integer: 
"1   qrsta"

CONTEXT:  COPY item_major, line 1, column id: "1   qrsta"





\echo 'COPY abc FROM STDIN WITH CSV HEADER;'
COPY
(
SELECT * FROM abc
)
to STDOUT
WITH delimiter E'\t'
\echo '\\.'





Sam Mason wrote:

On Tue, Oct 28, 2008 at 01:25:00PM +0200, Allan Kamau wrote:
 
The alternative I am attempting is to use "COPY abc FROM STDIN WITH 
HEADER". I pipe the contents of the CSV file on my PC to the psql 
command (that connects to the remote PC) while issuing this copy 
command.

This does seems not to work.



It does whenever I try it and if you've ever restored from a pg_dump
then you've used it as well!

 

Is there a way around it.



When I've had a CSV file and needed to bung it into a database, I've
tended to end up with shell scripts like this before:

  ( echo 'COPY abc FROM STDIN WITH CSV HEADER;'
cat "$1"
echo '\.'
  ) | psql

an alternative is to use the "\copy" feature inside psql that does this
sort of thing internally.  One thing to be aware of is that it doesn't
expect a semicolon at the end of the line, but is otherwise the same as
the SQL COPY command.


  Sam

  




are these space delimited values, or tab delimited values?
1   qrsta
2   zvyb
They are (tab delimited values), I've rechecked them, did a find and 
replace for any space between the columns with a tab, the error persists.


Allan.



test=# COPY abc FROM STDIN WITH CSV HEADER;
Enter data to be copied followed by a newline.
End with a backslash and a period on a line by itself.
>> 1,qrst,a
>> 2,zvy,b
>> \.
test=# select * from abc;
 id | strone | strtwo
++
  2 | zvy| b
(1 row)

test=#



--
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] Database access over the Internet...

[Reid Thompson]

Michelle Konzack wrote:

Am 2008-11-08 19:07:35, schrieb Scott Marlowe:

No, your histrionics aside, it's the way this list works by default,
and for good reason.  If you need it to work differently, there's a
setting which has been pointed out to you at two times now.  Please
take responsibility for your own life and fix the configuration and
stop whinging.


It does not work since the CCs are coming FROM the sender and  NOT  from
the mailinglist.


I'm thinking that if you check the setting per the faq that has been pointed 
out, that majordomo will note that you've been cc'd directly, and so will not 
send you the copy from the mailing list.


--
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] db backup script in gentoo

[Reid Thompson]

Scott Marlowe wrote:

On Sun, Nov 30, 2008 at 1:18 PM, Andrus <[EMAIL PROTECTED]> wrote:

Sorry, I'm no gentoo expert.  It's certainly not the first distro I'd
recommend to someone just starting out with linux / unix.  Time to
break out the google.  I also found quite a few example scripts for
lftp with google in a few minutes.

I must manage existing gentoo server where previous maintainer has left
company.
lftp is not found in gentoo and nobody knows how to activate it. So I must
use existing ftp.


It sounds like the last guy there was no gentoo expert either.
Apparently the install command is something like

emerge lftp


sudo emerge lftp

--
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] Data Directory size increasing abnormally

[Reid Thompson]

utsav wrote:

Dear All,

I am using postgres 7.3 version on RHEL 4.0.
 

You should upgrade to a newer/the latest stable release


The commanddata field consists of binary data of a txt file whose size 
is between 1kb to 4kb and there is a high frequecy of updates on this 
table (approx twice in a sec)
 
A strange behaviour is observerd in the physical files with respect to 
this table. The size of the file is growing abnormally in GBs. Suppose 
the file name (/oid of relation /)with respect to the table is "18924" I 
could find entries of 1 GB files like 18924, 18924.1, 18924.2 , 
18924.3..18924.40  in the data directory, though there are on 10k 
records in the table.
I have attached the of list command of the data directory.
Kindly help me in finding , why this is happening because, the server is 
running out of space.


Are you vacuuming this DB, or any of it's tables, especially the table in 
question?
 
Thanks and Regards

Utsav Turray




--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] number of page slots needed (1576544) exceeds max_fsm_pages (204800)]

[Reid Thompson]

Does this max_fsm_pages value seem OK for a 46GB database?
I've clustered all the tables that seemed to be exhibiting large amounts
of bloat.

reporting=# SELECT pg_size_pretty(pg_database_size('reporting'));
 pg_size_pretty 
 
  46 GB
  (1 row)

NOTICE:  number of page slots needed (1576544) exceeds max_fsm_pages (204800)
HINT:  Consider increasing the configuration parameter "max_fsm_pages" to a 
value over 1576544.


-- 
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] number of page slots needed (1576544) exceeds max_fsm_pages (204800)]

[Reid Thompson]

On Mon, Jan 18, 2010 at 02:43:11PM -0500, Vick Khera wrote:
> On Mon, Jan 18, 2010 at 1:47 PM, Reid Thompson  wrote:
> > Does this max_fsm_pages value seem OK for a 46GB database?
> > I've clustered all the tables that seemed to be exhibiting large amounts
> > of bloat.
> 
> 
> One more thing you may wish to consider is running re-index on your
> tables.  I found that a lot of pages with empty space were compacted
> and the number of fsm entries went down significantly when I did this
> last week.  For me this was more important than running cluster to
> pack the data tables themselves.

It was my belief that cluster would re-build the indexes as part of the
cluster operation.  Is that belief incorrect?

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] pg_database_size(oid)

[Reid Thompson]

On Wed, 2010-02-17 at 11:59 -0500, Dhimant Patel wrote:

> 
> I have created a database, which I plan to load with several tables. I
> am trying to find the size of the databases and came
> across pg_database_size(oid) function. Since it requires databse oid,
> I thought there must be a view where you get it - then came across
> "pg_database" table and still all in vain. The "pg_database" table
> lists all databases in the server but won't provide me the oid
> for pg_database_size(). 
> 
> 
> I noticed this being true for almost all reference views. For example,
> pg_tablespace lists the tablespaces but no tablespace oid.
> Function pg_tablespace_databases (tablespace_oid) ,
> needs tablespace_oid, but where I could get it from?

Use the db name.

test=# select pg_size_pretty(pg_database_size('test'));
 pg_size_pretty 

 5392 kB
(1 row)

test=# 

-- 
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 select return into PSQL variables.

[Reid Thompson]

On Thu, 2010-02-18 at 09:33 -0600, Little, Douglas wrote:
>   psql p1gp1 <>$LOGFile  2>&1 
> 
> \set ON_ERROR_STOP
> 
> select da_test.QATestBuild(false) 
> 
> QUIT 

mod to your needs...

$ cat dummy.sql 
#MYTESTID=`psql -t -c "select da_test.QATestBuild(false)" dbname`
MYTS=`psql -t -c "select to_char(now(), 'MMDDHH24MISS')" test |sed 's/^ //'`
#echo "[$MYTS]"
fn=`basename $0`
LOGFile=${fn}_${MYTS}.log

psql test <>$LOGFile  2>&1 

\set ON_ERROR_STOP

select * from stores;

QUIT


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] PostgreSQL fails to start

[Reid Thompson]

On 2/20/2010 4:42 PM, John Gage wrote:

I have had the same/similar problem on a Mac.  Postgres creates a user
"postgres" and the only way that user can see files is for them to exist
outside of any other particular user's home directory. I placed the
files in the root directory!? I would like, I think, to give "postgres"
privileges in my home directory (emphasis, I think). As a sign of
despicable laziness, could I ask where the granting of user privileges
is documented in the 8.4 docs?

John



directory/file permissions is an OS behavior, not a postgresql behavior.  Since OSX is unixen, you could make 
sure that you and postgres are are members of a common group.  Then, you can grant whatever group privileges 
you want to the files/directories that you want common access to.



--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] PostgreSQL fails to start

[Reid Thompson]

On 2/20/2010 4:42 PM, John Gage wrote:

I have had the same/similar problem on a Mac.  Postgres creates a user
"postgres" and the only way that user can see files is for them to exist
outside of any other particular user's home directory. I placed the
files in the root directory!? I would like, I think, to give "postgres"
privileges in my home directory (emphasis, I think). As a sign of
despicable laziness, could I ask where the granting of user privileges
is documented in the 8.4 docs?


http://ss64.com/osx/chmod.html

--
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]

[Reid Thompson]

On Tue, 2010-02-23 at 13:50 -0500, Shu Ho wrote:
> All
>  
> do you clean up the server file by removing them 
> use 
>  
> find $logfile -mtime +$NUMBER_DAYS_TO_KEEP  -type f -print -exec rm -f
> {} \; 
>  
> in postgres ?
>  
> How to remove the archive log files in postgres ? is the same way as
> remove backup files and server log files ?
>  
>  
> thanks
> Amy 
> 
> __
> Your E-mail and More On-the-Go. Get Windows Live Hotmail Free. Sign up
> now.

google logrotate
man logrotate

-- 
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 clear server log

[Reid Thompson]

On Tue, 2010-02-23 at 13:49 -0500, Amy Smith wrote:
> All
>  
> do you clean up the server file by removing them 
> use 

google logrotate
man logrotate


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Is this safe to perform on PostgreSQL 8.3.7 -> Resize a column in a PostgreSQL table without changing data

[Reid Thompson]

reporting=# select version();
 version
--
 PostgreSQL 8.3.7 on x86_64-redhat-linux-gnu, compiled by GCC gcc (GCC) 
4.1.2 20071124 (Red Hat 4.1.2-42)

(1 row)

I've a parent table with several years of monthly partitioned children. 
 There has arisen a need to increase the max size of a couple of 
varchar fields.  Total size of these tables is approaching ~200 GB, with 
the larger monthly tables approximately 7-10GB each.


Would it be safe to use the below process to accomplish this?
Whether I use the below method, or the standard
  ALTER TABLE mytable ALTER COLUMN mycolumn TYPE varchar(new_size);
my assumption is that I should apply the change first to the child 
tables, then to the parent???


From
http://sniptools.com/databases/resize-a-column-in-a-postgresql-table-without-changing-data

Resize a column in a PostgreSQL table without changing data

You use Post­greSQL. You find that a col­umn you have in a table is of a 
smaller length than you now wish. In my case, this was a varchar(20) 
that I now wished to make varchar(35). Noth­ing else. I just want to 
change the size, keep­ing the data intact.


The ALTER TABLE ...ALTER COLUMN...TYPE... com­mand is use­ful only if 
you want to alter the data some­how, or change the data type. 
Oth­er­wise, it'll be an aeon before this fin­ishes even inside a 
trans­ac­tion on a data­base of any mean­ing­ful size.


Until now, I was not famil­iar with any sen­si­ble mech­a­nism to 
sim­ply change the size in PG. But yes­ter­day, Tom Lane him­self 
sug­gested some­thing uber­cool in the list.


Let's assume for the sake of sim­plic­ity that your table is called 
"TABLE1" and your col­umn is "COL1". You can find the size of your 
"COL1" col­umn by issu­ing the fol­low­ing query on the sys­tem tables:


SELECT atttypmod FROM pg_attribute
WHERE attrelid = 'TABLE1'::regclass
AND attname = 'COL1';

atttypmod
---
24
(1 ROW)

This means that the size is 20 (4 is added for legacy rea­sons, we're 
told). You can now con­ve­niently change this to a varchar(35) size by 
issu­ing this command:


UPDATE pg_attribute SET atttypmod = 35+4
WHERE attrelid = 'TABLE1'::regclass
AND attname = 'COL1';

UPDATE 1

Note that I man­u­ally added the 4 to the desired size of 35..again, for 
some legacy rea­sons inside PG. Done. That's it. Should we check?


d TABLE1

TABLE "public.TABLE1"
COLUMN  |  TYPE | Modifiers
+---+---
COL1| CHARACTER VARYING(35) |

Such a sim­ple yet effec­tive trick. Of course it'd be nicer if this is 
some­how included in a more proper way in the data­base, but this does 
the job.


--
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 this safe to perform on PostgreSQL 8.3.7 -> Resize a column in a PostgreSQL table without changing data

[Reid Thompson]

On Tue, 2011-11-22 at 15:55 +, Gregg Jaskiewicz wrote:
> for the future it is better to just use text type, and: check
> length(field) < 35;

thanks to all for the respones.

The above seems a prudent way to go in my future.

My assumption is that converting varchar(n) to text would still force a
re-write of the table?  i.e. currently there's no officially 'safe' way
to convert the field type w/o incurring a table re-write.



-- 
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] Enumeration of tables is very slow in largish database

[Reid Thompson]

On Wed, 2012-01-11 at 20:50 +0100, Kirill Müller wrote:
> that would 
> allow tracing the queries and their runtime while they are executed \

http://www.postgresql.org/docs/8.4/static/auto-explain.html

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] PG9.1.2 -- 3 day old orphaned/non-responsive query -- pg_(cancel)(terminate)_backend has no effect

[Reid Thompson]

Looking for some quidance or suggestions.

PostgreSQL 9.1.2 on x86_64-unknown-linux-gnu, compiled by gcc (GCC) 4.4.5 
20110214 (Red Hat 4.4.5-6), 64-bit
$ uname -a
Linux db1.hw.ateb.com 2.6.32-131.21.1.el6.x86_64 #1 SMP Tue Nov 22 19:48:09 GMT 
2011 x86_64 x86_64 x86_64 GNU/Linux


   datid|  datname  | procpid |  usesysid  | usename  | application_name | 
client_addr  | client_hostname | client_port | backend_start |  
xact_start   |  query_start  | waiting |  
  current_query 
+---+-++--+--+--+-+-+---+---+---+-+--

 2034098332 | reporting |   30479 | 2033174751 | jbaucom  |  | 
172.16.48.79 | |1860 | 2012-01-10 10:55:03.784225-05 | 
2012-01-10 10:56:28.726281-05 | 2012-01-10 10:56:28.738283-05 | f   | s
elect * from data_vw where clientid = 164 and filldate > '01-01-2012'
 2034098332 | reporting |   32697 | 10 | postgres |  |  
| | | 2012-01-12 19:02:06.911721-05 | 
2012-01-12 19:02:06.941263-05 | 2012-01-12 19:02:06.941263-05 | f   | a
utovacuum: VACUUM public.data_201201
(2 rows)


There are 3 db's in this cluster. These above two activities are
essentially hitting the same underlying child table.  data_vw is on
data_parent which has data_MM children.

The select query has been apparently orphaned by it's originating
terminal or script or application.  pg_/cancel/terminate/_backend both
return t, but the query does not cancel/terminate.   sigterm via the OS
has no effect either.

I'm not sure whether the vacuum is active, and just slow, or if it's
spinning, or...?  I've taken no action toward the vacuum process.

reporting=# select count(*) from data_201201;
  count  
-
 9170468
(1 row)

There are approx 500K records inserted daily, so the table had approx
7.5M records when the query started.

$ cat /proc/30479/wchan 
sk_stream_wait_memory


Thanks,
reid


-- 
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 it even possible?

[Reid Thompson]

On Tue, 2012-03-20 at 09:22 -0500, Sam Loy wrote:
> I have now tried at least 7 different install methods to get pg up and 
> running on Lion. I fear that my system is now thoroughly inoculated and will 
> never be able to run postgres/postgis.
> 
> I started with the pg mac installer / stack builder. That worked to get pg 
> installed, but could not get postgis installed.
> 
> I've now tried two different instructions using MacPort, Two using homebrew, 
> and two using some-other-macport-homebrew-like method.
> 
> NONE of them worked for me. I can see postgress running from the last install:
> sameloyiv  39844   0.0  0.1  2455512   6496   ??  S 9:02AM   0:00.12 
> /usr/local/bin/postgres -D /usr/local/var/postgres -r 
> /usr/local/var/postgres/server.log -c unix_socket_directory=/var/pgsql_socket 
> -c unix_socket_group=_postgres -c unix_socket_permissions=0770
> root 104   0.0  0.0  2467372   1140   ??  Ss4:46PM   0:00.21 
> /opt/local/bin/daemondo --label=postgresql91-server --start-cmd 
> /opt/local/etc/LaunchDaemons/org.macports.postgresql91-server/postgresql91-server.wrapper
>  start ; --stop-cmd 
> /opt/local/etc/LaunchDaemons/org.macports.postgresql91-server/postgresql91-server.wrapper
>  stop ; --restart-cmd 
> /opt/local/etc/LaunchDaemons/org.macports.postgresql91-server/postgresql91-server.wrapper
>  restart ; --pid=none
> sameloyiv  40075   0.0  0.0  2434892548 s000  S+9:17AM   0:00.00 
> grep post
> sameloyiv  39849   0.0  0.0  2441352384   ??  Ss9:02AM   0:00.02 
> postgres: stats collector process 
> sameloyiv  39848   0.0  0.0  2455644   1564   ??  Ss9:02AM   0:00.02 
> postgres: autovacuum launcher process 
> sameloyiv  39847   0.0  0.0  2455512512   ??  Ss9:02AM   0:00.09 
> postgres: wal writer process 
> sameloyiv  39846   0.0  0.0  2455512604   ??  Ss9:02AM   0:00.12 
> postgres: writer process
> 
> But continue to see this when using psql:
> psql: could not connect to server: No such file or directory
>   Is the server running locally and accepting
>   connections on Unix domain socket "/tmp/.s.PGSQL.5432"?
> 
> Is there anyone who has ever successfully gotten postgres/postGIS running on 
> Mac Lion? Really? How? 
> Is there a way to purge my system of all of the corrupted/bad installs and 
> start over? How?
> 
> How do I get pgadmin if I use homebrew/macports etc?
> 
> Would love to believe this is possible, as I cannot stand having to stop 
> whatever I'm doing every 3-4 days to repair Windows. I hate Windows. I loath 
> Windows, and would love to see the U.S. Economy rebound from all of the 
> production gained by everyone universally abandoning Windows…

unix_socket_directory=/var/pgsql_socket vs /tmp/.s.PGSQL.5432

you need to tell psql where the unix socket is located, or you need to
configure tcp to be available and use the tcp port to connect


-- 
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] Use PSQLFS for photo storage

[Reid Thompson]

On Tue, 2009-01-13 at 18:22 -0600, Jason Long wrote:
> Never used Python or Perl.  I use primarily Java.  I was thinking of
> doing something like
> INSERT INTO pictures (filename,data) VALUES 
> ('filename','/path/to/my/image/img0009.jpg');
> 
> But, this syntax doesn't seem to be supported.
> 
> Maybe I can use a custom C function to get the contents of the file.  Then do 
> something like
> 
> INSERT INTO pictures (filename,data) VALUES 
> ('/path/to/my/image/img0009.jpg',getBinaryFileContents('/path/to/my/image/img0009.jpg'));
> 
> Is there some postgres contrib for something like this?
> 
> 
> 
> 
Simple java stub test program attached.  Modify to meet your needs.  See
file header for URL of original example.
/**
 * A demo program to show how jdbc works with postgresql
 * Nick Fankhauser 10/25/01
 * ni...@ontko.com or n...@fankhausers.com
 * This program may be freely copied and modified
 * Please keep this header intact on unmodified versions
 * The rest of the documentation that came with this demo program
 * may be found at http://www.fankhausers.com/postgresql/jdbc
 */



import java.sql.*;   // All we need for JDBC
import java.text.*;
import java.io.*;

public class HelloPostgresql
{
  Connection   db;// A connection to the database
  Statementsql;   // Our statement to run queries with
  DatabaseMetaData dbmd;  // This is basically info the driver delivers
  // about the DB it just connected to. I use
  // it to get the DB version to confirm the
  // connection in this example.

  public HelloPostgresql(String argv[])
throws ClassNotFoundException, SQLException
  {
String database = argv[0];
String username = argv[1];
String password = argv[2];
String imagefilepath = argv[3];
//System.out.println("img fp: "+imagefilepath+"\n");

Class.forName("org.postgresql.Driver"); //load the driver
db = DriverManager.getConnection("jdbc:postgresql:"+database,
 username,
 password); //connect to the db
dbmd = db.getMetaData(); //get MetaData to confirm connection
System.out.println("Connection to "+dbmd.getDatabaseProductName()+" "+
   dbmd.getDatabaseProductVersion()+" successful.\n");
sql = db.createStatement(); //create a statement that we can use later

String sqlText = "create table jdbc_demo_bytea (imgname text, img bytea)";
PreparedStatement ps = db.prepareStatement(sqlText);
System.out.println("Executing this command: "+sqlText+"\n");
sql.executeUpdate(sqlText);

File file = new File(imagefilepath);

//System.out.println("filepath: "+file.getPath()+"\n");
String dupimagefilepath = new String(file.getPath() + "_Duplicate");
//System.out.println("dup img fp: "+dupimagefilepath+"\n");
try {
FileInputStream fis = new FileInputStream(file);
long startts = System.currentTimeMillis();
ps = db.prepareStatement("INSERT INTO jdbc_demo_bytea VALUES (?, ?)");
ps.setString(1, file.getName());
ps.setBinaryStream(2, fis, (int)file.length());
int rows_inserted = ps.executeUpdate();
//System.out.println("inserted: "+rows_inserted+"\n");
ps.close();
long stopts = System.currentTimeMillis();
System.out.println("Store time = " + (stopts - startts));
fis.close();
}
catch ( Exception e)
{
System.out.println(e.toString());
}

//try {
// Thread.currentThread().sleep(1000);
long startts = System.currentTimeMillis();
ps = db.prepareStatement("SELECT img FROM jdbc_demo_bytea WHERE imgname = ?");
ps.setString(1, imagefilepath);

ResultSet rs = ps.executeQuery();
ResultSetMetaData rsmd = rs.getMetaData();
//System.out.println("fp : "+ rsmd.getColumnName(1) +"\n");
//System.out.println("#columns : "+rsmd.getColumnCount()+"\n");
while (rs.next()) {
//System.out.println("got next\n");
//byte[] imgBytes = rs.getBytes(1);
byte[] imgBytes = new byte[4000];
imgBytes = rs.getBytes(1);
long stopts = System.currentTimeMillis();
System.out.println("Retreive time = " + (stopts - startts));
try {
File dupfile = new File(dupimagefilepath );
//System.out.println("write it : "+imgBytes.length+"\n");
OutputStream fos = new FileOutputStream (dupfile ) ;
fos.write(imgBytes);
fos.close();
fos = null;
}
catch ( Exception e)
{
System.out.println(e.toString());
}
}
rs.close();
//}
   // catch(InterruptedException ie){
//If this thread was intrrupted by nother thread
//}

ps.close();
sqlText = "drop table jdbc_d

Re: [GENERAL] [findings] minimal open source e-commerce software for pg

[Reid Thompson]

Ivan Sergio Borgonovo wrote:

On Sat, 7 Feb 2009 01:43:10 +0100
Ivan Sergio Borgonovo  wrote:


I need to build up a minimal e-commerce website on a host that is
already running postgresql.

Requirement is minimal. Usual configurable pretty standard
couple of paying/shipping system and popular enough to find
cheap/free skins and very basic cms feature to display no more than
4-5 pages.

This has to be a quick and dirty job and the actual requirement are
just the above.


After googling and downloading some of the most popular open source
ecommerce software I didn't find any that works out of the box with
PostgreSQL.
Theoretically since drupal support postgresql ecommerce and ubercart
modules, they should support postgresql too. But that's just theory,
reality is different.

BTW I just put a B2B ecommerce engine in production that use drupal
as the output layer and strictly PostgreSQL for all the business
logic.
When I had to choose the DB... PostgreSQL really seemed the best
candidate (maybe the only in the open source world).
I consider ecommerce more similar to accounting than cms, and in fact
among accounting programs Postgresql is much more popular.

That's why I'm pretty surprised I couldn't find any open source
product that could run out of the box.

I'm so surprised I'm thinking I didn't do well my homework and I'm
still here to ask if anyone know any open source ecommerce software
that run on PostgreSQL out of the box.


may be too heavy, but Apache OFBiz quotes support for PostgreSQL 
http://ofbiz.apache.org/


--
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] Matching against a field case in-sensitive.

[Reid Thompson]

James Dooley wrote:

Hi,

Since PostgreSQL is by default case sensitive, I would like to know if 
it is possible to do a search or somehow set the column in a relation to 
be case insensitive (on search at least).


I would however like to store it case sensitive so I can display it as 
it was stored.


Ps. I am not interested in creating another column.






select value from table where relation ilike 'search-criteria'

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] how can i migrate just the users from one db to a new one

[Reid Thompson]

I've a DB and I want to migrate the users to a new empty DB.  How do I
dump/reload just the users?

Thanks,
reid

-- 
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] running postgresql on a private machine accessing it from public web pages

[Reid Thompson]

Keith D. Evans wrote:
We usually run postgresql on a private machine (gravity) but due to 
space, we have moved the database to a different machine through afs (a 
local network at the university). We do not want the private machine to 
be accessible to outside users, so our web pages are on the university's 
public web pages. They have mysql, but won't install postgresql, which 
we've been using on gravity for years.


We want to add a database where users can get and plot data using php 
over the internet. But since the public computers don't have postgresql, 
can we have postgresql running on gravity allowing users accessing 
through the internet on the public pages to access the data?


thanx,
keith



gravity = postgresql host = private
webpages = university web server = public

so, user accesses webpage on university web server, the webpage ( webapp ) 
has/or creates a connection to the postgresql server on gravity --> yes, user 
can access webpage on public server and pull data from postgresql server on 
private server ( as long as postgresql is configured to accept the connection 
from the webserver and network connectivity exists between public and private 
server )


Does this answer what you are asking?


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] pg_dump: ERROR: Memory exhausted in AllocSetAlloc(875574064)

[Reid Thompson]

Is there any solution to this other than adding memory, or am I
mis-understanding the error?

pg_dump: ERROR:  Memory exhausted in AllocSetAlloc(875574064)
pg_dump: lost synchronization with server, resetting connection
pg_dump: SQL command to dump the contents of table "table_name" failed:
PQendcopy() failed.
pg_dump: Error message from server: pg_dump: The command was: COPY
"table_name" TO stdout;

v7.2.3 on RHAT 7.3 linux with 128MB RAM.

Thanks,
reid




---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings

Re: [GENERAL] pg_dump: ERROR: Memory exhausted in AllocSetAlloc(875574064)

[Reid Thompson]

Tom Lane wrote:
> "Reid Thompson" <[EMAIL PROTECTED]> writes:
>> Is there any solution to this other than adding memory, or am I
>> mis-understanding the error?
> 
>> pg_dump: ERROR:  Memory exhausted in AllocSetAlloc(875574064)
>> pg_dump: lost synchronization with server, resetting connection
> 
> My bet is that this is actually a corrupt-data problem ...
> unless you actually have any 875MB fields in your table.  The
> implication is that the length word of a variable-width field
> contains garbage.  More than likely, the whole tuple is
> garbaged, but this happens to be the first visible symptom.
> 
> There is plenty of discussion of recovering from data
> corruption in the archives, so go have a look.
> 
>> v7.2.3 on RHAT 7.3 linux with 128MB RAM.
> 
> Might want to think about an update sometime, too ;-)
> 
>   regards, tom lane

No, no 875MB fields -- the largest field is limited to 2048 chars.
Heading to the archives.

thanks,

reid

---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster

[GENERAL] DB/clog corruption

[Reid Thompson]

I've a db table generating the error below on pg_dump.  Per the
archives, i've tried creating a clog file 1559 but w/o success (
continue to get the same error) even when i extended the clog file to >
64K.  Are there other options to recovering this table?  Do i need to
extend clog 1559 to a greater size?  Do i have to 'do something' to
create a non-zero length record at 1/669E0568? ( if so, how?)

thanks,
reid

$ pg_dump -f table_dump.sql -t table_with_error dbname
FATAL 2:  read of clog file 1559, offset 0 failed: Success
pg_dump: FATAL 2:  read of clog file 1559, offset 0 failed: Success
pg_dump: lost synchronization with server, resetting connection
DEBUG:  server process (pid 1672) exited with exit code 2
DEBUG:  terminating any other active server processes
DEBUG:  all server processes terminated; reinitializing shared memory
and semaphores
DEBUG:  database system was interrupted at 2005-07-11 08:41:39 EDT
DEBUG:  checkpoint record is at 1/669E0528
DEBUG:  redo record is at 1/669E0528; undo record is at 0/0; shutdown
TRUE
DEBUG:  next transaction id: 1237894; next oid: 2850421
DEBUG:  database system was not properly shut down; automatic recovery
in progress
FATAL 1:  The database system is starting up
pg_dump: SQL command to dump the contents of table "table_with_error"
failed: PQendcopy() failed.
pg_dump: Error message from server: FATAL 1:  The database system is
starting up

pg_dump: The command was: COPY "table_with_error" TO stdout;
$ DEBUG:  ReadRecord: record with zero length at 1/669E0568
DEBUG:  redo is not required
DEBUG:  database system is ready




---(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] DB/clog corruption

[Reid Thompson]

Tom Lane wrote:
> "Reid Thompson" writes:
>> $ pg_dump -f table_dump.sql -t table_with_error dbname
>> FATAL 2:  read of clog file 1559, offset 0 failed: Success
> 
> You've got a clobbered tuple header in that file (ridiculous
> xmin or xmax value).  Alternatively, given the evident age of
> this server, it could be a clobbered page header leading to
> the system following a bogus pointer to a tuple header.
> 
> I think the track you want to pursue is identifying which
> page contains the bad data and looking at it with pg_filedump
> to see if there's anything recoverable there at all.  If not,
> just zeroing out the page is probably the easiest way of
> getting to a dumpable state.  You've probably lost this
> particular tuple in any case, you might or might not have lost the
> whole page. 
> 
> Once you get out of this, you might want to think about
> updating to something newer than 7.2 ...
> 
>   regards, tom lane

DB corruption resolution.

PG_VERSION: 7.2.3

First, I would like to very much thank Tom Lane for guiding me in
resolving this.

Issues:   A procedural error resulted in filesystem full ( out of disk
space )
  resulting in inability to create pg_clog file(s)
  There also existed a corrupted page in a single table.

Symptoms:  pg_dump failure, query "select * from" failure, vacuum
failure
   all around "Oh crap"

Disclaimer:  If you are having a DB issue, do NOT assume that the steps
outlined below will resolve it for you.  The mailing list archives note
that there is not 'one procedure' for recovery, most incidents are
unique in some way.  Many incidents may require certain 'same steps' to
be performed, but there is no cookie cutter resolution.  Search the
archives, read the threads regarding incidents that are similar to or
match yours and request help from the mailing list if you feel it is
warranted.  This synopsis is meant to, hopefully, add to the
understanding one gets when searching the archives for issues it
addresses.

Initial symptom noted:

pg_dump: FATAL 2:  open of /postgres/data/pg_clog/0202 failed: No such
file or directory
pg_dump: lost synchronization with server, resetting connection
pg_dump: SQL command to dump the contents of table "table_name_here"
failed: PQendcopy() failed.
pg_dump: Error message from server: FATAL 1:  The database system is
starting up

pg_dump: The command was: COPY "table_name_here" TO stdout;
ERROR: pg_dump "dbname"
ERROR: backup failed

Before performing any work I used pg_dump to individually dump all other
tables in the database.

First steps were to address the pg_clog file issues.
Note:  PG Versions 7.3 and above should not require manual intervention
for pg_clog files after an out of space condition.
Procedures outlined in the mailing list archives provided for this.
Querying the mailing list archives for dd bs= pg_clog , etc should
provide ample reading material on extending the file in the proper bs
size increments for your situation.  My pg instance used the default 8k
block size...  so,
  shutdown the db 
  dd bs=8k count=1 < /dev/zero >>/postgres/data/pg_clog/0202
  startup the db
  ... Note that the final size of your pg_clog file may need to
something other than 8k, see the various threads in the archives related
to this issue.

Getting past this yielded a different error:

$ pg_dump -f table_dump.sql -t table_with_error dbname
FATAL 2:  read of clog file 1559, offset 0 failed: Success
pg_dump: FATAL 2:  read of clog file 1559, offset 0 failed: Success
pg_dump: lost synchronization with server, resetting connection
DEBUG:  server process (pid 1672) exited with exit code 2
DEBUG:  terminating any other active server processes
DEBUG:  all server processes terminated; reinitializing shared memory
and semaphores
DEBUG:  database system was interrupted at 2005-07-11 08:41:39 EDT
DEBUG:  checkpoint record is at 1/669E0528
DEBUG:  redo record is at 1/669E0528; undo record is at 0/0; shutdown
TRUE
DEBUG:  next transaction id: 1237894; next oid: 2850421
DEBUG:  database system was not properly shut down; automatic recovery
in progress FATAL 1:  The database system is starting up
pg_dump: SQL command to dump the contents of table "table_with_error"
failed: PQendcopy() failed.
pg_dump: Error message from server: FATAL 1:  The database system is
starting up

pg_dump: The command was: COPY "table_with_error" TO stdout;
$ DEBUG:  ReadRecord: record with zero length at 1/669E0568
DEBUG:  redo is not required
DEBUG:  database system is ready

Research in the archives, followed by a post to and receipt of response
from the mailing list, resulted in using oid2name and pg_filedump to
foster a resolution.  oid2name was from the contrib source for my pg
version.  oid2name was utilized to get the pg filename for the affe

Re: [GENERAL] Insert ImageFile in PostgreSQL

[Reid Thompson]

[EMAIL PROTECTED] wrote:


I'm sorry, but I know only the basics about DBMs, but I think is not
possible to put a image file INSIDE a database. You can put in a
database the path of the file...

(Maybe I'm wrong...)

See ya!


---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

  http://www.postgresql.org/docs/faq
 


See the postgresql org docs for the bytea datatype.

---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

  http://www.postgresql.org/docs/faq

[GENERAL] postgresql hosting

[Reid Thompson]

Someone questioned a while back about hosting providers for PostgreSQL
-- a recent post led to finding http://www.planetargon.com/hosting/



---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings

Re: [GENERAL] Select gives the wrong results

[Reid Thompson]

> Crystle Numan wrote:
>> Dear all:
>> 
>> I am fairly knowledgeable about PostgreSQL but this behaviour is
>> stumping me. Any help would be wonderful. If you think it is a bug, 
>> let me now and I'll file one.
>> 
>> (select values in DB (date stamps) between Jan 1, 2000 and Jan 1,
>> 2005, no results)
>> 
>> db_name=# SELECT * from person_detail WHERE field='2' AND
>> value>'946702800' AND value<'1104555600';
>>  id | person | field | value
>> ++---+---
>> (0 rows)
>> 
>> (select values in DB (date stamps) between Jan 1, 2003 and Jan 1,
>> 2005, 4 results (!))
>> 
>> db_name=# SELECT * from person_detail WHERE field='2' AND
>> value>'1041397200' AND value<'1104555600';
>>   id  | person | field |   value
>> --++---+
>>  1187 |454 | 2 | 1051156800
>>  1188 |460 | 2 | 1053316800
>>  1219 |472 | 2 | 1057723200
>>  1181 |441 | 2 | 1042520400
>> (4 rows)
>> 
>> The first select should have those 4 results plus any more. We tried
>> putting quotes (") around the word 'value' to see if that made a 
>> difference, and no it didn't. We tried reversing the two clauses and 
>> that made no difference.
>> 
>> Here's another funny one. Not the one that doesn't belong.
>> 
>> db_name=# SELECT * from person_detail WHERE field='2' AND
>> value>='11' AND value<='1104555600';
>>   id  | person | field |   value
>> --++---+
>> 3 |218 | 2 | 1017464400
>>   253 |295 | 2 | 1002340800
>>   514 |323 | 2 | 100155600
>>  1126 |405 | 2 | 1006750800
>>  1179 |439 | 2 | 1035172800
>>  1187 |454 | 2 | 1051156800
>>  1188 |460 | 2 | 1053316800
>>  1219 |472 | 2 | 1057723200
>>  1181 |441 | 2 | 1042520400
>>  1152 |434 | 2 | 1032321600
>>  1129 |410 | 2 | 1024027200
>> (11 rows)
>> 
>> Anyone see what's going on here?
>> 
>> Thanks!
>> Crystle
>> 
>> 
>> 
>http://archives.postgresql.org


Assuming that value is epoch date, 
 
 $ psql test -c "select date(1051156800)"
 date
 
  2003-04-24
 (1 row)
 
 $ psql test -c "select date(1053316800)"
 date
 
  2003-05-19
 (1 row)
 
 $ psql test -c "select date(0)"
 date
 
  1969-12-31
 (1 row)
 
 $ psql test -c "select date(86400)"
 date
 
  1970-01-01
 (1 row)

Then wouldn't something along the lines of:
SELECT * from person_detail WHERE field='2' AND value between
date(11) and date(1104555600); work

reid


---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faq

Re: [GENERAL] newbie - postgresql or mysql

[Reid Thompson]

Michael Fuhr wrote:
> [Please copy the mailing list on replies so others can
> contribute to and learn from the discussion.  Also, please
> don't top-post, as it destroys the flow of the discussion;
> I've moved your questions to a more logical place.]
> 
> On Wed, Aug 31, 2005 at 03:49:57PM -0600, [EMAIL PROTECTED] wrote:
>> On Wed, 31 Aug 2005, Michael Fuhr wrote:
>>> On Wed, Aug 31, 2005 at 03:17:29PM -0400, Frank wrote:
>> insert  into category values
>> (4, 'Hardware - Monitor', 2, '2004-10-12 10:50:01'),
>> (5, 'Hardware - Printer', 2, '2004-10-12 10:50:02'),
>> (6, 'Hardware - Terminal', 2, '2004-10-12 10:50:02'),
>>> 
>>> PostgreSQL doesn't support this form of INSERT; you'll have to use a
>>> separate INSERT per record or use COPY.  Another method, although
>>> probably not useful in this case, is to insert records from a
>>> subquery; see the INSERT documentation for more information.
>> 
>> Wouldn't it be simpler to do a dblink, and just get the data from
>> MySQL and drop it into PostgreSQL ? Or is this too complicated ?
> 
> dblink (at least the one distributed as contrib/dblink) is
> for making connections to other PostgreSQL databases.  You
> could, however, use DBI-Link or something similar to make
> connections to MySQL or another data source; in that case you
> could use the subquery form of INSERT:
> 
> INSERT INTO tablename (columnlist) SELECT columnlist FROM ... ;
> 
> However, if you're just doing a one-time import of data from
> MySQL, then it might be simplest to dump the data with
> separate INSERT statements (mysqldump --extended-insert=FALSE).

If you have time to play around a bit, and have Ruby, og could be
utilized...
create a test env, do a little hacking on the below ( from the og
examples dir) i.e. you'd want to remove the destroy => true, or set to
false, in the config, etc...

$ cat mysql_to_psql.rb
# = Mysql to PostgreSQL migration example.
#
# A simple example to demonstrate the flexibility of
# Og. Two connections to different databases are 
# created and data is copied from a MySQL database
# to a PostgreSQL database.
#
# Og makes it easier to switch to a REAL database :)

require 'og'

# Configure databases.

psql_config = {
  :destroy => true,
  :name => 'test',
  :store => 'psql',
  :user => 'postgres',
  :password => 'navelrulez'
}

mysql_config = {
  :destroy => true,
  :name => 'test',
  :store => 'mysql',
  :user => 'root',
  :password => 'navelrulez'
}

# Initialize Og.

psql = Og.connect(psql_config)
mysql = Og.connect(mysql_config)

# An example managed object.
# Looks like an ordinary Ruby object.

class Article
  property :name, :body, String

  def initialize(name = nil, body = nil)
@name, @body = name, body
  end
end

# First populate the mysql database.

mysql.manage(Article)

a1 = Article.create('name1', 'body1')
a1 = Article.create('name1', 'body1')
a1 = Article.create('name1', 'body1')

# Read all articles from Mysql.

articles = Article.all

# Switch to PostgreSQL.

psql.manage(Article)

# Store all articles.

for article in articles
  article.insert
end

# Fetch an article from PostgreSQL
# as an example. Lookup by name.

article = Article.find_by_name('name1')

reid

---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faq

Re: [GENERAL] ECPG examples...

[Reid Thompson]

Title: Message



see 
the contrib directory,,, i think there is also a simple example in the 
docs...
 
 
reid 

  
  -Original Message-From: Cristian Prieto 
  [mailto:[EMAIL PROTECTED] Sent: Thursday, September 01, 
  2005 4:10 PMTo: pgsql-general@postgresql.orgSubject: 
  [GENERAL] ECPG examples...
  Hello, I've been reading a little the ECPG 
  (Embedded SQL in C) and the doc is (I guess) very clear, but I cannot find any 
  examples in the documentation, any idea where to get examples? inside the 
  pgsql source code?
   
  Thanks a 
lot!

Re: [GENERAL] ECPG examples...

[Reid Thompson]

Title: Message



http://tutorials.findtutorials.com/read/id/185/headline/Professional+Linux+Programming+Part+3+-+ECPG 
 
 
reid 

  
  -Original Message-From: Cristian Prieto 
  [mailto:[EMAIL PROTECTED] Sent: Thursday, September 01, 
  2005 4:10 PMTo: pgsql-general@postgresql.orgSubject: 
  [GENERAL] ECPG examples...
  Hello, I've been reading a little the ECPG 
  (Embedded SQL in C) and the doc is (I guess) very clear, but I cannot find any 
  examples in the documentation, any idea where to get examples? inside the 
  pgsql source code?
   
  Thanks a 
lot!

[GENERAL] If an index is based on 3 columns will a query using two of the columns utilize the index?

[Reid Thompson]

Example:
assume a table of 10 columns, three of which are fname, lname, and dob.
If an index is created on (fname, lname, dob), will a query that
utilizes two of the columns ( select 'data' from table where fname = 'X'
and lname = 'Y') utilize the index? 

thanks,
reid



---(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] If an index is based on 3 columns will a query using two of the columns utilize the index?

[Reid Thompson]

Alvaro Herrera wrote:
> On Mon, Sep 12, 2005 at 09:43:57AM -0400, Reid Thompson wrote:
>> Example:
>> assume a table of 10 columns, three of which are fname, lname, and
>> dob. If an index is created on (fname, lname, dob), will a query that
>> utilizes two of the columns ( select 'data' from table where fname =
>> 'X' and lname = 'Y') utilize the index?
> 
> Yes, if it is selective enough.  (It _can_ use the index,
> which does not mean that it _will_ use it.)  Note that if
> your example query used the columns (lname, dob), the answer would be
> "no." 
> 
Why is that? In order to use an index, does the query have to utilize
the 'first' element of the index?



reid

---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faq

Re: [GENERAL] Utility that creates table schema from csv data?

[Reid Thompson]

Richard Huxton wrote:
> CSN wrote:
>> Probably wishful thinking, but who knows - maybe
>> there's something in contrib! I have a bunch of csv
>> data with the field names specified on the first line
>> of the various files. Is there any such utility that
>> will create a table schema using the field names AND
>> look through the data and determine what data types
>> each field should be?
> 
> I'm not sure that's possible in a general sense. Is 123 text or a
> number? How do I prove it one way or another?
> 
> However, if you were happy to end up with "good enough" types (i.e.
> might end up using text instead of a more specific type), and
> know that
> data was representative then it shouldn't be too tricky to write some
> pattern-matching Perl to do the job.

ruby's auto-typing might do the same, w/o having to write
pattern-matching.

reid

---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings

Re: [GENERAL] Arrrr... date formatting.

[Reid Thompson]

Title: Message



provide an example of what you're trying to 
do.
 
test=>  select 
date_part('month',current_date);    date_part 
--- 9(1 
row)
 
test=> select 
length(date_part('month',current_date)); length 
  1(1 row)
 
 
 
 
reid 

  
  -Original Message-From: Alex Turner 
  [mailto:[EMAIL PROTECTED] Sent: Monday, September 19, 2005 11:21 
  AMTo: PostgreSQL ListSubject: [GENERAL] A... date 
  formatting.Shiver me timbers, whats up with 
  to_char()?!Avast!  Is there no way to get a month without it 
  being padded to nine characters?  It makes using a database to actualy 
  format a date for output kinda impossible?I'll have to walk the plank 
  if I can't get an answer, the crew will mutiny and leave me on an island with 
  a one-shot pistol and the sight of me sails dissapearing over the 
  horizon!Alex TurnerNetEconomistP.S. For those who didn't know 
  - it's national talk like a pirate day.

Re: [GENERAL] character varying == text?

[Reid Thompson]

Scott Marlowe wrote:
> On Mon, 2005-09-19 at 12:54, Alex Turner wrote:
>> I thought a char field was supposed to return a padded string, and
>> varchar was supposed to return a non-padded string?
>> 
>> I just checked though:
>> 
>> create table test (
>> stuff char(10)
>> );
>> 
>> insert into test values ('foo');
>> 
>> select stuff || 'lemon' from test;
>> 
>> This returns 'foolemon', not 'foolemon' as I would have
>> expected. 
>> 
>> Alex Turner
>> NetEconomist
>> 
>> On 9/15/05, Tom Lane <[EMAIL PROTECTED]> wrote:
>> CSN <[EMAIL PROTECTED]> writes:
>> > Just something I was curious about - is there any
>> > difference at all between "character varying" (in the
>> > SQL spec) without a length specified and "text" (not  
>> > in the SQL spec)? 
>> 
>> The SQL standard doesn't allow "character varying" without a
>> length spec. 
>> 
>> But yeah, in Postgres they're essentially the same thing.
>> 
>> regards, tom lane
>> 
>> ---(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
>> 
> 
> That's because || is a text operator, not a char operator
> here.  So, what's really happening is:
> 
> select cast(charfield as text)||cast(textfield as text)
> 
> 
> 
> ---(end of
> broadcast)---
> TIP 2: Don't 'kill -9' the postmaster

is this the expected result? i.e. in the past was the result incorrect?
or is there a configuration param that controls this?


test=# select stuff || 'lemon' from test1;
?column? 
-
 foo   lemon
(1 row)

test=# \d test1
   Table "test1"
 Column | Type  | Modifiers 
+---+---
 stuff  | character(10) | 

test=# select version();
   version   
-
 PostgreSQL 7.2.3 on i686-pc-linux-gnu, compiled by GCC 2.96
(1 row)


test=# select stuff || 'lemon' from test;
 ?column?
--
 foolemon
(1 row)

test=# \d test
Table "public.test"
 Column | Type  | Modifiers
+---+---
 stuff  | character(10) |

test=# select version();
 version



--
 PostgreSQL 8.0.1 on i686-pc-mingw32, compiled by GCC gcc.exe (GCC)
3.4.2 (mingw
-special)
(1 row)

reid

---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faq

[GENERAL] Does copy abort on a failed insertion?

[Reid Thompson]

If i have a pg_dump that is using COPY, and midway through the copy an
insert fails due to a unique constraint, will the COPY continue with the
rest of the records, or will it abort?  If it aborts, will it perform a
rollback on the inserts up to that point?

thanks,
reid

---(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