how to install just client libraries on windows?

hi,

i just tried to upgrade libpq.dll on windows and couldn't
work out how to do it. in the past, with 8.3.x, i knew i had
to download the entire server even though i only wanted
the client but at least i was able to install just the
client software.

now, with 8.4 (enterprisedb one click installer), i tried
to deselect what looked like server-related options and
kept the client-related options selected but the installer
kept telling me that something i had selected required
the server to also be selected. i had to unselected all of the
options before that error went away. since none of the options
was just the client library i thought there was a remote
chance that it might make some sense especially when the
installer happily proceeded with the installation process
even though no components were selected. i figured it must
know something i didn't 

anyway, all that was installed was the stackbuilder.
there was no PostgresPlus/8.4SS/bin/libpq.dll as i
had expected.

so, what have i done wrong? how do i use the one click
installer to just install libpq.dll? i don't mind installing
other client software along with it. i just don't want the
server installed. should i install the server and client
software and then uninstall the server software?

using column as 'mutex'

I want to use a dbase record in Postgresql as a mutex in a garbage collection/file upload system.

If I tranfer an uploaded file to a directory, write all its meta information and file name in a record, the
user should have 5 minutes to finish inputting the required info for the record. (I want to store in file
system instead of database table).

So there will be two processes racing against each other in the database, the web page changing the status of
the record to 'complete', and the cron job erasing files that have status 'in-process' and are older than
ten minutes.

So there will probably be 6 possible scenarios:

1/ Web site changes status before the cron job gets to the record and deltes the file, no problem.
2/ Cron job changes the record and deletes the file before the website gets to the record, no problem.

3/ Both access the the record relatively at the same time, the web site having the earlier, lower
transaction number and also finishes first.
4/ Both access the the record relatively at the same time, the cron job having the earlier, lower
transaction number and also finishes first.

5/ Both access the the record relatively at the same time, the web site having the later, HIGHER transaction
number BUT finishes first.
6/ Both access the the record relatively at the same time, the cron job having the later, HIGHER transaction
number BUT finishes first.

Do I have the possiblities correctly described? What happens with cases 3-6?

Thanks in advance. I RTFMed and Googled, but did not see the answer.

Dennis Gearon

Re: duplicating a schema

On Sun, Nov 29, 2009 at 21:21, Ivan Sergio Borgonovo
<mail <at> webthatworks.it> wrote:
> I need to create a new schema with all the content in an existing
> one, just with a new name.
>
> The way I've found is:
> - make a backup
> - load it in a dev box
> - rename the schema
> - make a backup of the new schema
> - restore the new schema on the original DB.
>
> Is there a more efficient approach?

Sadly no. With smaller DBs I do a sed on the dump ... wished there
would be a restore with not only a target DB but also a target schema.

--

-- 
★ Clemens 呉 Schwaighofer
★ IT Engineer/Web Producer/Planning
★ E-Graphics Communications SP Digital
★ 6-17-2 Ginza Chuo-ku, Tokyo 104-8167, JAPAN
★ Tel: +81-(0)3-3545-7706
★ Fax: +81-(0)3-3545-7343
★ http://www.e-gra.co.jp

This e-mail is intended only for the named person or entity to which
it is addressed and contains valuable business information that is 
privileged, confidential and/or otherwise protected from disclosure. 
If you received this e-mail in error, any review, use, dissemination,

Re: limiting resources to users

Craig Ringer wrote:
> I assume you look up the associated backend by looking up the source 
> IP and port of the client with `netstat', `lsof', etc, and matching 
> that to pg_stat_activity?
There's a bunch of ways I've seen this done:

1) If you spawn the psql process with bash using "&", you can then find 
its pid with "$!", then chain through the process tree with ps and 
pg_stat_activity as needed to figure out the backend pid.
2) If you know the query being run and it's unique (often the case with 
batch jobs run daily for example), you can search for it directly in the 
query text of pg_stat_activity.
3) Sometimes the only queries you want to re-nice are local, while 
everything else is remote.  You might filter down possible pids that way.
4) Massage data from netstat, lsof, or similar tools to figure out which 
process you want.

> It makes me wonder if it'd be handy to have a command-line option for 
> psql that caused it to spit the backend pid out on stderr.
Inspired by this idea, I just thought of yet another approach.  Put this 
at the beginning of something you want to track:

COPY (SELECT pg_backend_pid()) TO '/place/to/save/pid';

Not so useful if there's more than one of the query running at once, but 
in the "nice a batch job" context it might be usable.

--

-- 
Greg Smith    2ndQuadrant   Baltimore, MD
PostgreSQL Training, Services and Support

Re: limiting resources to users

On 1/12/2009 11:33 AM, Greg Smith wrote:
> Craig Ringer wrote:
>> I assume you look up the associated backend by looking up the source
>> IP and port of the client with `netstat', `lsof', etc, and matching
>> that to pg_stat_activity?
> There's a bunch of ways I've seen this done:
>
> 1) If you spawn the psql process with bash using "&", you can then find
> its pid with "$!", then chain through the process tree with ps and
> pg_stat_activity as needed to figure out the backend pid.

I feel like I'm missing something obvious here. How can walking the 
process tree starting with the psql pid help you find the backend pid?
The backend and client have no relationship in the process tree. At some 
point you have to match the (ip,port) tuple for the client's connection 
against pg_stat_activity - what you've listed separately as (4). Even 
that won't help if a unix socket is in use, since client_addr is null 
and client_port is -1.

So: knowing the client (say, psql) pid, how can you find the backend pid 
without relying on something like lsof or netstat to identify the source 
(ip,port) combo used by the particular client instance whose pid you know?

 > 4) Massage data from netstat, lsof, or similar tools to figure out
 > which process you want.

IOW, how is this distinct from (1) ?

>> It makes me wonder if it'd be handy to have a command-line option for
>> psql that caused it to spit the backend pid out on stderr.

Re: using column as 'mutex'

On Mon, Nov 30, 2009 at 9:02 PM, Dennis Gearon <gearond <at> sbcglobal.net> wrote:
> I want to use a dbase record in Postgresql as a mutex in a garbage collection/file upload system.
>
> If I tranfer an uploaded file to a directory, write all its meta information and file name in a record, the
user should have 5 minutes to finish inputting the required info for the record. (I want to store in file
system instead of database table).
>
> So there will be two processes racing against each other in the database, the web page changing the status
of the record to 'complete', and the cron job erasing files that have status 'in-process' and are older
than ten minutes.
>
> So there will probably be 6 possible scenarios:
>
> 1/ Web site changes status before the cron job gets to the record and deltes the file, no problem.
> 2/ Cron job changes the record and deletes the file before the website gets to the record, no problem.
>
> 3/ Both access the the record relatively at the same time, the web site having the earlier, lower
transaction number and also finishes first.
> 4/ Both access the the record relatively at the same time, the cron job having the earlier, lower
transaction number and also finishes first.
>
> 5/ Both access the the record relatively at the same time, the web site having the later, HIGHER
transaction number BUT finishes first.
> 6/ Both access the the record relatively at the same time, the cron job having the later, HIGHER
transaction number BUT finishes first.

Just make sure that when you inspect the record, do so: a. in a
transaction (preferably a brief one) and b. WITH UPDATE until your
operation completes.  That way if two operations collide one will wait
for the other to complete.  If you need to lock an external structure

Re: duplicating a schema

On Tue, 1 Dec 2009 11:39:06 +0900
Schwaighofer Clemens <clemens.schwaighofer <at> tequila.jp> wrote:

> On Sun, Nov 29, 2009 at 21:21, Ivan Sergio Borgonovo
> <mail <at> webthatworks.it> wrote:
> > I need to create a new schema with all the content in an existing
> > one, just with a new name.

> > The way I've found is:
> > - make a backup
> > - load it in a dev box
> > - rename the schema
> > - make a backup of the new schema
> > - restore the new schema on the original DB.

> > Is there a more efficient approach?

> Sadly no. With smaller DBs I do a sed on the dump ... wished there
> would be a restore with not only a target DB but also a target
> schema.

I thought about sed but I think postgresql parse better SQL than me
and sed together.
Why do you prefer sed over backup/restore on smaller DB?

I didn't test this... but I think it could be even better if I
wouldn't prefer to have a full backup before such operation:

pg_dump -dmydb --schema=XXX -Fp > XXX.bak

Re: how to install just client libraries on windows?

On Tue, Dec 1, 2009 at 1:52 AM, raf <raf <at> raf.org> wrote:
> hi,
>
> i just tried to upgrade libpq.dll on windows and couldn't
> work out how to do it. in the past, with 8.3.x, i knew i had
> to download the entire server even though i only wanted
> the client but at least i was able to install just the
> client software.
>
> now, with 8.4 (enterprisedb one click installer), i tried
> to deselect what looked like server-related options and
> kept the client-related options selected but the installer
> kept telling me that something i had selected required
> the server to also be selected. i had to unselected all of the
> options before that error went away. since none of the options
> was just the client library i thought there was a remote
> chance that it might make some sense especially when the
> installer happily proceeded with the installation process
> even though no components were selected. i figured it must
> know something i didn't 
>
> anyway, all that was installed was the stackbuilder.
> there was no PostgresPlus/8.4SS/bin/libpq.dll as i
> had expected.
>
> so, what have i done wrong? how do i use the one click
> installer to just install libpq.dll? i don't mind installing
> other client software along with it. i just don't want the
> server installed. should i install the server and client
> software and then uninstall the server software?

Re: limiting resources to users

On 1 Dec 2009, at 4:55, Craig Ringer wrote:

> On 1/12/2009 11:33 AM, Greg Smith wrote:
>> Craig Ringer wrote:
>>> I assume you look up the associated backend by looking up the source
>>> IP and port of the client with `netstat', `lsof', etc, and matching
>>> that to pg_stat_activity?
>> There's a bunch of ways I've seen this done:
>> 
>> 1) If you spawn the psql process with bash using "&", you can then find
>> its pid with "$!", then chain through the process tree with ps and
>> pg_stat_activity as needed to figure out the backend pid.
> 
> I feel like I'm missing something obvious here. How can walking the process tree starting with the psql pid
help you find the backend pid?
> The backend and client have no relationship in the process tree. At some point you have to match the
(ip,port) tuple for the client's connection against pg_stat_activity - what you've listed separately
as (4). Even that won't help if a unix socket is in use, since client_addr is null and client_port is -1.
> 
> So: knowing the client (say, psql) pid, how can you find the backend pid without relying on something like
lsof or netstat to identify the source (ip,port) combo used by the particular client instance whose pid
you know?

I think Greg intended this to be used from the process that needs re-nicing. If you have a batch job that you
always want to run at a different nice-level you can get its pid that way and use that to re-nice the process.

You can also match it up to procpid in pg_stat_activity to get other info about the backend, but I don't think
there's anything in there that you'd need to know at that point (you got the pid to re-nice already, after all).

Alban Hertroys

WARNING: worker took too long to start; cancelled on VACCUM ANALYZE