Re: [GENERAL] Rules, Windows and ORDER BY

[Jason Dusek]

2012/8/23 Tom Lane :
> Jason Dusek  writes:
>> I have a simple table of keys and values which periodically
>> receives updated values. It's desirable to keep older values
>> but, most of the time, we query only for the latest value of a
>> particular key.
>
>>   CREATE TABLE kv
>>   ( k bytea NOT NULL,
>> at timestamptz NOT NULL,
>> realm bytea NOT NULL,
>> v bytea NOT NULL );
>>   CREATE INDEX ON kv USING hash(k);
>>   CREATE INDEX ON kv (t);
>>   CREATE INDEX ON kv USING hash(realm);
>
>>   SELECT * FROM kv WHERE k = $1 AND realm = $2 ORDER BY at DESC LIMIT 1;
>
> If you want to make that fast, an index on (k,realm,at) would
> help.  Those indexes that you did create are next to useless
> for this, and furthermore hash indexes are quite unsafe for
> production.

Thanks for pointing out the unsafety of hash indexes. I think I
got in the habit of using them for a project with large,
temporary data sets.

Why are the individual indices not useful? The tests that the
query does -- equality on key and realm and ordering on at --
are each supported by indices. Does it have to do with the cost
of loading the three indices?

--
Jason Dusek
pgp // solidsnack // C1EBC57DC55144F35460C8DF1FD4C6C1FED18A2B


-- 
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] Rules, Windows and ORDER BY

[Martijn van Oosterhout]

On Fri, Aug 24, 2012 at 09:32:32AM +, Jason Dusek wrote:
> 2012/8/23 Tom Lane :
> > Jason Dusek  writes:
> >>   CREATE TABLE kv
> >>   ( k bytea NOT NULL,
> >> at timestamptz NOT NULL,
> >> realm bytea NOT NULL,
> >> v bytea NOT NULL );
> >>   CREATE INDEX ON kv USING hash(k);
> >>   CREATE INDEX ON kv (t);
> >>   CREATE INDEX ON kv USING hash(realm);
> >
> >>   SELECT * FROM kv WHERE k = $1 AND realm = $2 ORDER BY at DESC LIMIT 1;
> >
> > If you want to make that fast, an index on (k,realm,at) would
> > help.  Those indexes that you did create are next to useless
> > for this, and furthermore hash indexes are quite unsafe for
> > production.
> 
> Why are the individual indices not useful? The tests that the
> query does -- equality on key and realm and ordering on at --
> are each supported by indices. Does it have to do with the cost
> of loading the three indices?

I'm not entirely sure, but I'll take a stab at it. I think it has to do
with the fact that you want order. Combining multiple indexes so you
use them at the same time works as an BitmapAnd. That is, it uses each
index to determine blocks that are interesting and then find the blocks
that are listed by all tindexes, and then it loads the blocks and chcks
them.

The problem here is that you want ORDER BY at, which makes the above
scheme fall apart, because order is not preversed. So it falls back on
either scanning the 'at' index and probing checking the rows to see if
they match, or using all indexes, and then sorting the result.

In theory you could BitmapAnd the 'k' and 'realm' indexes and then scan
the 'at' index only checking rows that the bitmap shows are
interesting.  But I'm not sure if postgres can do that.

Anyway, the suggested three column index will match your query in a
single lookup and hence be much faster than any of the above
suggestions, so if this is a really important query then it may be
worth it here.

Have a nice day,
-- 
Martijn van Oosterhout  http://svana.org/kleptog/
> He who writes carelessly confesses thereby at the very outset that he does
> not attach much importance to his own thoughts.
   -- Arthur Schopenhauer


signature.asc
Description: Digital signature

Re: [GENERAL] FETCH in subqueries or CTEs

[Craig Ringer]

On 08/24/2012 12:34 PM, Pavel Stehule wrote:


you can't mix planned and unplanned statements together - think about
stored plans every time


Thanks Pavel and Jeff.

I can't say I fully understand the arguments, but I'll take it that 
accepting cursors in CTEs or subqueries wouldn't make sense. I guess the 
main issue really is that you'd have to materialize them anyway to avoid 
issues with multiple scans, so there's little point having a cursor.


I didn't find a reasonable way to simply fetch a cursor into a (possibly 
temporary) table, like:


INSERT INTO sometable FETCH ALL FROM somecursor;

... which could be handy with PL/PgSQL functions that return multiple 
refcursors. It only seems to be possible via a PL/PgSQL wrapper that 
loops over the cursor and returns a rowset.


--
Craig Ringer


--
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] Windows SIngle Sign On - LINUX Server

[SUNDAY A. OLUTAYO]

LDAP will be your best choice for SSO, Ubuntu Linux can authenticate against AD.

Also this is OS stuff not PostgreSQL, if you server is in production and you 
can not handle this migration,
it is advisable that you subscribe for support on Ubuntu from canonical 


Thanks, 

Sunday Olutayo 





- Original Message -
From: "Jeremy Palmer" 
To: pgsql-general@postgresql.org
Sent: Thursday, August 23, 2012 8:12:55 PM
Subject: [GENERAL] Windows SIngle Sign On - LINUX Server

Hi All,

We are currently running PostgreSQL 8.4 on Windows server 2003 and are planning 
to move the instance to Ubuntu 10.4 - yay!. At the same time we will also 
upgrade to 9.1. One nice features that we leverage from the windows 
configuration is the ability for windows clients to use AD SSO i.e SSPI. This 
was really easy to set-up and configure.

If we move to Linux I was wondering if anyone could provide a howto reference 
or some tips on how to set-up auth configuration to provide SSO to windows 
clients? I've read the GSSAPI/Kerberos authentication section of the docs, but 
it's still unclear to me the exact process to follow. In particular what things 
would I need to configure on the Ubuntu server and which tasks will need to be 
done on the Windows domain controller (which is managed by a third party 
service provider who knows little about PostgreSQL or Ubuntu)

We are using a WIndows server 2008 for the domain control. However I know 
little about it's setup or configuration, I only know it's our windows domain 
realm. 

Regards,
Jeremy
This message contains information, which is confidential and may be subject to 
legal privilege. If you are not the intended recipient, you must not peruse, 
use, disseminate, distribute or copy this message. If you have received this 
message in error, please notify us immediately (Phone 0800 665 463 or 
i...@linz.govt.nz) and destroy the original message. LINZ accepts no 
responsibility for changes to this email, or for any attachments, after its 
transmission from LINZ. Thank You.


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


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

[GENERAL] I: Installation faillure version 8.4.12

[Segato Luca]

 

Dear All

We try to install, several times, postgres version 8.4.12, each
installation was failed during the "post-install step "

 displaying this error:  (install-postgresql.log)

 

Executing cscript //NoLogo
"F:\postgressql/installer/server/initcluster.vbs" "postgres" "postgres"
"" "F:\postgressql" "F:\postgressql\data" 5432 "DEFAULT"

Script exit code: 1

 

Script output:

 Errore CScript: Impossibile trovare l'interprete di script "VBScript"
per lo script "F:\postgressql\installer\server\initcluster.vbs".

 

Script stderr:

 Program ended with an error exit code

 

Error running cscript //NoLogo
"F:\postgressql/installer/server/initcluster.vbs" "postgres" "postgres"
"" "F:\postgressql" "F:\postgressql\data" 5432 "DEFAULT" : Program
ended with an error exit code

Problem running post-install step. Installation may not complete
correctly

 The database cluster initialisation failed.

 

We tried with  command: regsvr32 %systemroot%\system32\vbscript.dll  but
the problem was not solved

 

Could you suggest us , some solutions ?

 

Best regards

 

Luca Segato

 

Dr. Luca Segato 
R.E.A.C.H. Manager 
  
Tel. +39 02 99442252 
Fax. +30 02 99442265 

  
lseg...@prholding.it 
http://www.prholding.it 
  
P&R HOLDING S.P.A. 
HEADQUARTERS
Via Milano, 186 
20024 Garbagnate Milanese (MI) - ITALY

 


DICHIARAZIONE DI RISERVATEZZA
Le informazioni contenute nella comunicazione che precede e negli eventuali 
allegati possono essere riservate e sono, comunque, destinate alla persona o 
all'ente indicati come destinatari.
La diffusione, copiatura e/o distribuzione del documento trasmesso da parte di 
qualsiasi soggetto diverso dal destinatario e proibita e potra essere 
legalmente persegutia.Se avete ricevuto questo messaggio per errore, Vi 
preghiamo di contattarci immediatamente e di distruggere la comunicazione e 
tutti gli eventuali allegati

CONFIDENTIAL STATEMENT 
This e-mail and any attachements may contain confidential and/or proprietary 
information that are for the exclusive use of the intended recipient only.The 
disclosure, copying, distribution or any other use of this e-mail by any other 
than the intended recipient is strictly prohibited and may result in legal 
action.
If you have recived this e-mail in error, please immediately inform us then 
delete the e-mail and all enclosed attachments

Re: [GENERAL] I: Installation faillure version 8.4.12

[Ashesh Vashi]

On Fri, Aug 24, 2012 at 4:47 PM, Segato Luca  wrote:

>  ** **
>
> Dear All
>
> We try to install, several times, postgres version 8.4.12, each
> installation was failed during the “post-install step ”
>
>  displaying this error:  (install-postgresql.log)
>
> ** **
>
> *Executing cscript //NoLogo
> "F:\postgressql/installer/server/initcluster.vbs" "postgres" "postgres"
> "" "F:\postgressql" "F:\postgressql\data" 5432 "DEFAULT"*
>
> *Script exit code: 1*
>
> * *
>
> *Script output:*
>
> * **Errore CScript: Impossibile trovare l'interprete di script "VBScript"
> per lo script "F:\postgressql\installer\server\initcluster.vbs".*
>
> * *
>
> *Script stderr:*
>
> * Program ended with an error exit code*
>
> * *
>
> *Error running cscript //NoLogo
> "F:\postgressql/installer/server/initcluster.vbs" "postgres" "postgres"
> "" "F:\postgressql" "F:\postgressql\data" 5432 "DEFAULT" : Program
> ended with an error exit code*
>
> *Problem running post-install step. Installation may not complete
> correctly*
>
> * The database cluster initialisation failed.*
>
> * *
>
> We tried with  command*: **regsvr32 %systemroot%\system32\vbscript.dll**
> **but the problem was not solved***
>
> * *
>
> *Could you suggest us , some solutions ?*
>
Can you attach the installation logs?
And - also mention the operating system.
Can
--

Thanks & Regards,

Ashesh Vashi
EnterpriseDB INDIA: Enterprise PostgreSQL Company



*http://www.linkedin.com/in/asheshvashi*

**
>
> * *
>
> *Best regards*
>
> * *
>
> *Luca Segato***
>
> ** **
>
> *Dr. Luca Segato*
> *R.E.A.C.H. Manager*
>
> Tel. +39 02 99442252
> Fax. +30 02 99442265
>
>
> *lseg...@prholding.it*
> *http://www.prholding.it*
>
> *P&R HOLDING S.P.A.*
> *HEADQUARTERS*
> *Via Milano, 186*
> *20024 Garbagnate Milanese (MI) - ITALY*
>
> ** **
>  *DICHIARAZIONE DI RISERVATEZZA*
> Le informazioni contenute nella comunicazione che precede e negli
> eventuali allegati possono essere riservate e sono, comunque, destinate
> alla persona o all'ente indicati come destinatari.
> La diffusione, copiatura e/o distribuzione del documento trasmesso da
> parte di qualsiasi soggetto diverso dal destinatario è proibita e potrà
> essere legalmente perseguita. Se avete ricevuto questo messaggio per
> errore, Vi preghiamo di contattarci immediatamente e di distruggere la
> comunicazione e tutti gli eventuali allegati
>
> *CONFIDENTIAL STATEMENT*
> This e-mail and any attachments may contain confidential and/or
> proprietary information that are for the exclusive use of the intended
> recipient only.The disclosure, copying, distribution or any other use of
> this e-mail by any other than the intended recipient is strictly prohibited
> and may result in legal action. If you have received this e-mail in error,
> please immediately inform us then delete the e-mail and all enclosed
> attachments
>

Re: [GENERAL] Interval "1 month" is equals to interval "30 days" - WHY?

[Dmitry Koterov]

BTW there are a much more short version of this:

CREATE OR REPLACE FUNCTION int_equal(interval, interval) RETURNS boolean
IMMUTABLE STRICT LANGUAGE sql AS
'SELECT $1::text = $2::text';


On Wed, Aug 8, 2012 at 4:51 PM, Albe Laurenz wrote:

> Then maybe you should use something like this for equality:
>
> CREATE OR REPLACE FUNCTION int_equal(interval, interval) RETURNS boolean
>   IMMUTABLE STRICT LANGUAGE sql AS
>   'SELECT
> 12 * EXTRACT (YEAR FROM $1) + EXTRACT (MONTH FROM $1)
>   = 12 * EXTRACT (YEAR FROM $2) + EXTRACT (MONTH FROM $2)
> AND EXTRACT (DAY FROM $1) = EXTRACT (DAY FROM $2)
> AND 36 * EXTRACT (HOUR FROM $1)
> + 6000 * EXTRACT (MINUTE FROM $1)
> + EXTRACT (MICROSECONDS FROM $1)
>   = 36 * EXTRACT (HOUR FROM $2)
> + 6000 * EXTRACT (MINUTE FROM $2)
> + EXTRACT (MICROSECONDS FROM $2)';
>
>

Re: [GENERAL] Windows SIngle Sign On - LINUX Server

[Craig Ringer]

On 08/24/2012 06:10 PM, SUNDAY A. OLUTAYO wrote:

LDAP will be your best choice for SSO, Ubuntu Linux can authenticate against AD.


I'm not at all convinced by that. Active Directory functions as a 
Kerberos KDC. Kerberos provides secure authentication and (unlike LDAP) 
single sign-on.


http://technet.microsoft.com/en-us/library/bb742516.aspx

Use Kerberos via GSSAPI. Here's a good starting point by Marcus:

http://www.hagander.net/talks/Deploying%20PostgreSQL%20in%20a%20Windows%20Enterprise.pdf

--
Craig Ringer


--
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] Windows SIngle Sign On - LINUX Server

[SUNDAY A. OLUTAYO]

In real world deployment, LDAP and Kerbero are often combined for 
authentication and authorization.

The link below is a well documented howto:

https://help.ubuntu.com/community/SingleSignOn


Thanks,

Sunday Olutayo 


- Original Message -
From: "Craig Ringer" 
To: "SUNDAY A. OLUTAYO" 
Cc: "Jeremy Palmer" , pgsql-general@postgresql.org
Sent: Friday, August 24, 2012 12:48:01 PM
Subject: Re: [GENERAL] Windows SIngle Sign On - LINUX Server

On 08/24/2012 06:10 PM, SUNDAY A. OLUTAYO wrote:
> LDAP will be your best choice for SSO, Ubuntu Linux can authenticate against 
> AD.

I'm not at all convinced by that. Active Directory functions as a 
Kerberos KDC. Kerberos provides secure authentication and (unlike LDAP) 
single sign-on.

http://technet.microsoft.com/en-us/library/bb742516.aspx

Use Kerberos via GSSAPI. Here's a good starting point by Marcus:

http://www.hagander.net/talks/Deploying%20PostgreSQL%20in%20a%20Windows%20Enterprise.pdf

--
Craig Ringer


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

[GENERAL] create table like . . . constraint names

[Sahagian, David]

Is there any way for me to control the name of the (unique or primary) 
constraints that get created when doing a "create table like parent-table" 
statement ?

I use this statement to create the new table in a different schema than the one 
in which the parent-table lives, and I would like the constraint names to be 
the same as those of the parent-table.

thanks,
-dvs-

Re: [GENERAL] FETCH in subqueries or CTEs

[Pavel Stehule]

2012/8/24 Craig Ringer :
> On 08/24/2012 12:34 PM, Pavel Stehule wrote:
>
>> you can't mix planned and unplanned statements together - think about
>> stored plans every time
>
>
> Thanks Pavel and Jeff.
>
> I can't say I fully understand the arguments, but I'll take it that
> accepting cursors in CTEs or subqueries wouldn't make sense. I guess the
> main issue really is that you'd have to materialize them anyway to avoid
> issues with multiple scans, so there's little point having a cursor.
>
> I didn't find a reasonable way to simply fetch a cursor into a (possibly
> temporary) table, like:
>
> INSERT INTO sometable FETCH ALL FROM somecursor;

it should be implemented as function - like materialize_cursor(cursor, table)

I would to see full support of stored procedures (with multirecordsets) rather.

Regards

Pavel

>
> ... which could be handy with PL/PgSQL functions that return multiple
> refcursors. It only seems to be possible via a PL/PgSQL wrapper that loops
> over the cursor and returns a rowset.
>
> --
> Craig Ringer


-- 
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] FETCH in subqueries or CTEs

[Tom Lane]

Craig Ringer  writes:
> I didn't find a reasonable way to simply fetch a cursor into a (possibly 
> temporary) table, like:
> INSERT INTO sometable FETCH ALL FROM somecursor;

Why would you bother with a cursor, and not just INSERT ... SELECT
using the original query?

Putting a cursor in between will just make matters more complicated and
slower.  (For one thing, the plan created for a cursor is optimized for
incremental fetching not read-it-all-at-once.)

regards, tom lane


-- 
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] Rules, Windows and ORDER BY

[Tom Lane]

Martijn van Oosterhout  writes:
> On Fri, Aug 24, 2012 at 09:32:32AM +, Jason Dusek wrote:
>> Why are the individual indices not useful? The tests that the
>> query does -- equality on key and realm and ordering on at --
>> are each supported by indices. Does it have to do with the cost
>> of loading the three indices?

> I'm not entirely sure, but I'll take a stab at it. I think it has to do
> with the fact that you want order. Combining multiple indexes so you
> use them at the same time works as an BitmapAnd. That is, it uses each
> index to determine blocks that are interesting and then find the blocks
> that are listed by all tindexes, and then it loads the blocks and chcks
> them.

Yeah.  While you *can* in principle solve the problem with the
individual indexes, it's much less efficient than a single index.
In particular, BitmapAnd plans are far from being a magic bullet
for combining two individually-not-very-selective conditions.
(That realm constraint is surely not very selective; dunno about
the key one.)  That implies reading a large number of entries from
each index, forming a rather large bitmap for each one, and then
ANDing those bitmaps to get a smaller one.  And even after all that
work, you're still not done, because you have no idea which bit in
the bitmap represents the row with largest "at" value.

> In theory you could BitmapAnd the 'k' and 'realm' indexes and then scan
> the 'at' index only checking rows that the bitmap shows are
> interesting.  But I'm not sure if postgres can do that.

No, it can't, and that likely wouldn't be a very effective plan anyway;
you could end up scanning a very large fraction of the "at" index, since
you'd have to start at the end (the latest entry anywhere in the table).
Even if you didn't make many trips to the heap, that's not cheap.

In constrast, given a three-column btree index organized with the
equality-constrained columns first, the btree code can descend the
index tree straight to the entry you want.  We've expended a lot of
sweat on optimizing that case, and it will absolutely blow the doors
off anything involving a bitmap scan.

Of course the downside is that the three-column index might be
relatively useless for queries of forms other than this one.
So it's a tradeoff between flexibility and performance.  But since
the OP is asking, I'm assuming he cares a lot about performance of
queries of this exact form.

regards, tom lane


-- 
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] Check PostgreSQL status using MS-DOS bat file?

[Loughrey, Hugh]

Hi Dinesh,

Managed to get this up and running...thanks!!

Regards

Hugh

From: dinesh kumar [mailto:dineshkuma...@gmail.com]
Sent: 17 August 2012 13:15
To: Loughrey, Hugh
Cc: Postgres General
Subject: Re: [GENERAL] Check PostgreSQL status using MS-DOS bat file?

Hi ,

Dave's instructions are helpful for finding the status of the server.. However, 
I do have the below the script which is nothing but PgPing in windows ... I 
hope it helps you in the implementation ..



@ECHO OFF

set PSQL="C:\Program Files\PostgreSQL\9.1\bin"

set DBNAME="template1"

set USER="postgres"

set PORT="5432"

set RES="Not Pinging"

%PSQL%\psql -Atq -c "SELECT 'ping'" -p %PORT% -U %USER% %DBNAME% > _Res.txt

set /p RES=<_Res.txt

echo %RES%

IF %RES% EQU ping (echo "No need to raise any exception ") else (echo 
"PostgreSQL seems not pinging.. Need to raise an exception")


Best Regards,
Dinesh
manojadinesh.blogspot.com

On Fri, Aug 17, 2012 at 4:32 PM, Dave Page 
mailto:dp...@pgadmin.org>> wrote:
[Please keep the mailing list CC'd]

On Fri, Aug 17, 2012 at 11:52 AM, Loughrey, Hugh
mailto:hugh.lough...@hoopleltd.co.uk>> wrote:
> Hi Dave,
>
> Thanks for the message below. The script you forwarded looks to be for an 
> instance in which the DB is running of a windows box, apologies I should have 
> mentioned, we currently run PostgreSQL on a Linux box. Does this mean calling 
> pg_ctl is not an option? Or do we need to install additional drivers?

pg_ctl only checks the status of an instance running on the local
machine. To check on a remote linux box from windows using pg_ctl,
you'd have to run pg_ctl on the linux box, probably over SSH (look for
Putty for an SSH client for Windows).

An easier option might be to run a psql command on the remote
database. Just run something like "SELECT 1" and check you actually
get a 1 back, and not a connection error.

> We'd be using FME to push data from other databases into PostgreSQL, however 
> before running our FME scripts we'd want to check the DB is up and running. 
> If the DB is running, then run the FME scripts, if not...don't run the FME 
> scripts. All of this needs to be controlled via a windows server 2008 box.
>
> I appreciate your help.
>
> Regards
>
> Hugh
>
> -Original Message-
> From: Dave Page [mailto:dp...@pgadmin.org]
> Sent: 15 August 2012 16:30
> To: Bruce Momjian
> Cc: Loughrey, Hugh; 
> pgsql-general@postgresql.org
> Subject: Re: [GENERAL] Check PostgreSQL status using MS-DOS bat file?
>
> On Wed, Aug 15, 2012 at 4:04 PM, Bruce Momjian 
> mailto:br...@momjian.us>> wrote:
>> On Wed, Aug 15, 2012 at 09:52:17AM +, Loughrey, Hugh wrote:
>>> Hi All,
>>>
>>> I want to write a MS-DOS command to check that the PostgreSQL
>>> database is up and running and able to accept data being pushed to
>>> it. From a bit of reading I've identified the pg_ctl status command,
>>> but can this be incorporated into a *.bat file and can the resulting
>>> status be recorded in a *.txt file? If so does anyone have a command which 
>>> would enable this?
>>
>> As I remember it is pretty tricky to call pg_ctl from a Windows batch
>> file.  I know the Windows installers do it somehow --- you might want
>> to downlaod it and see if you can find the shell script they use.
>> Dave Page might know more --- CC'ing him.
>
> You shouldn't try to start/stop the server with pg_ctl if it's configured to 
> run as a service (use "net start xxx", "net stop xxx"
> for that), but you can check the status:
>
> C:\>"C:\Program Files\PostgreSQL\9.2\bin\pg_ctl.exe" -D "C:\Program 
> Files\PostgreSQL\9.2\data" status
> pg_ctl: server is running (PID: 1040)
> C:/Program Files/PostgreSQL/9.2/bin/postgres.exe "-D" "C:/Program 
> Files/PostgreSQL/9.2/data"
>
>
> C:\>net stop postgresql-x64-9.2
> The postgresql-x64-9.2 service is stopping.
> The postgresql-x64-9.2 service was stopped successfully.
>
>
> C:\>"C:\Program Files\PostgreSQL\9.2\bin\pg_ctl.exe" -D "C:\Program 
> Files\PostgreSQL\9.2\data" status
> pg_ctl: no server running
>
> --
> Dave Page
> Blog: http://pgsnake.blogspot.com
> Twitter: @pgsnake
>
> EnterpriseDB UK: http://www.enterprisedb.com The Enterprise PostgreSQL Company
> "Any opinion expressed in this e-mail or any attached files are those of the 
> individual and not necessarily those of Hoople Ltd. You should be aware that 
> Hoople Ltd. monitors its email service. This e-mail and any attached files 
> are confidential and intended solely for the use of the addressee. This 
> communication may contain material protected by law from being passed on. If 
> you are not the intended recipient and have received this e-mail in error, 
> you are advised that any use, dissemination, forwarding, printing or copying 
> of this e-mail is strictly prohibited. If you have received this e-mail in 
> error please contact the sender immediately and destroy all copies of it.


--
Dave Page
Blog: http://pgsnak

Re: [GENERAL] At what point does a big table start becoming too big?

[Gavin Flower]

On 23/08/12 11:06, Nick wrote:

I have a table with 40 million rows and haven't had any performance issues yet.

Are there any rules of thumb as to when a table starts getting too big?

For example, maybe if the index size is 6x the amount of ram, if the table is 
10% of total disk space, etc?



I think it would be good to specify the context.

For example:
The timeliness of a database required to support an ship based 
anti-missile system would require far more stringent timing 
considerations than a database used to retrieve scientific images based 
on complicated criteria.


The size of records, how often updated/deleted, types of queries, ... 
would also be useful.


Unfortunately it might simply be a case of "It depends..."!


Cheers,
Gavin


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

[GENERAL] run function on server restart

[John D. West]

Sometimes the server process crashes and restarts, usually when I run some
large calculations that eat up all available memory. Is there any way to
detect this and run a cleanup routine when it happens? Running 8.4 on
Ubuntu.

 -- John

[GENERAL] Overlapping time ranges constraints in 8.4

[EXT-Rothermel, Peter M]

I have a temporal data question that may be much easier to handle in version 
9.x but I am stuck on version 8.4.

One table has a time range that is implemented as start_time and end_time 
columns of type TIMESTAMP with Timezone.
A second table has information that is needed to determine if there is a 
schedule conflict in the items in the first table.

I am considering using row level INSERT and UPDATE triggers to prevent 
overlapping time ranges.

TABLE campus (
   id SERIAL,
   foo BOOLEAN NOT NULL,
   ...
   PRIMARY KEY (id)
) ;


TABLE B (
  id SERIAL,
  campus_id INTEGER NOT NULL,
  start_time timestamp NOT NULL,
 stop_time timestamp NOT NULL,
 ...
  PRIMARY KEY (id),
  FOREIGN KEY (campus_id) REFERENCES campus(id) ON DELETE CASCADE;
);

Records in table B are not considered overlapping if their campus has its foo 
column set to FALSE.

In my triggers (PL/pgSQL) I am using a expression like this

  SELECT B.* INTO v_overlapping from INNER JOIN campus ON 
(campus.id=B.campus_id)
 where campus.colA = 't' AND (campus.start_time, campus.stop_time) 
OVERLAPS (NEW.start_time, NEW.stop_TIME);

I am worried that the transaction serialization will not do the predicate 
locking that is needed for concurrent inserts/updates.
Can I use add a FOR UPDATE clause to my SELECT INTO expression in PL/pgSQL ?

Pete Rothermel

Re: [GENERAL] Overlapping time ranges constraints in 8.4

[Kevin Grittner]

"EXT-Rothermel, Peter M"  wrote:
 
> I have a temporal data question that may be much easier to handle
> in version 9.x but I am stuck on version 8.4.
 
That is unfortunate.  Getting this to work correctly in 8.4 will
probably be a lot more work than upgrading to 9.1 and getting it to
work there.
 
> One table has a time range that is implemented as start_time and
> end_time columns of type TIMESTAMP with Timezone.
> A second table has information that is needed to determine if
> there is a schedule conflict in the items in the first table.
> 
> I am considering using row level INSERT and UPDATE triggers to
> prevent overlapping time ranges.
> 
> TABLE campus (
>id SERIAL,
>foo BOOLEAN NOT NULL,
>...
>PRIMARY KEY (id)
> ) ;
> 
> 
> TABLE B (
>   id SERIAL,
>   campus_id INTEGER NOT NULL,
>   start_time timestamp NOT NULL,
>  stop_time timestamp NOT NULL,
>  ...
>   PRIMARY KEY (id),
>   FOREIGN KEY (campus_id) REFERENCES campus(id) ON DELETE CASCADE;
> );
> 
> Records in table B are not considered overlapping if their campus
> has its foo column set to FALSE.
> 
> In my triggers (PL/pgSQL) I am using a expression like this
> 
>   SELECT B.* INTO v_overlapping from INNER JOIN campus ON 
> (campus.id=B.campus_id)
>  where campus.colA = 't' AND (campus.start_time, 
> campus.stop_time) OVERLAPS (NEW.start_time, NEW.stop_TIME);
> 
> I am worried that the transaction serialization will not do the
> predicate locking that is needed for concurrent inserts/updates.
 
To get that sort of predicate locking in PostgreSQL, you must be
using version 9.1 or later and the transactions must be using the
serializable transaction isolation level.  But for something like
this, you might be better off using the "exclusion constraint"
feature of 9.0 and later.  (The only reason I say "might" instead of
"would" is that I'm not sure that feature can handle the
complication of the boolean in a separate table.)
 
> Can I use add a FOR UPDATE clause to my SELECT INTO expression in
> PL/pgSQL ?
 
That won't help -- it just locks the actual rows read; it doesn't
protect against insertion of conflicting rows.  You could use
explicit locking to actually serialize the transactions which do
this.  There are other options, but none of them are pretty.
 
-Kevin


-- 
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] run function on server restart

[Kevin Grittner]

"John D. West"  wrote:
 
> Sometimes the server process crashes and restarts, usually when I
> run some large calculations that eat up all available memory.
 
You might want to reconfigure to avoid that.
 
> Is there any way to detect this and run a cleanup routine when it
> happens?
 
What is it that are you trying to clean up?
 
-Kevin


-- 
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] Windows SIngle Sign On - LINUX Server

[Jeremy Palmer]

Marcus' guide looks great. 

So what's the pros/cons of using the Kerberos via GSSAPI method, rather than 
going for the SingleSignOn method mentioned by Sunday? 

From: SUNDAY A. OLUTAYO [olut...@sadeeb.com]
Sent: Saturday, 25 August 2012 12:00 a.m.
To: Craig Ringer
Cc: Jeremy Palmer; pgsql-general@postgresql.org
Subject: Re: [GENERAL] Windows SIngle Sign On - LINUX Server

In real world deployment, LDAP and Kerbero are often combined for 
authentication and authorization.

The link below is a well documented howto:

https://help.ubuntu.com/community/SingleSignOn


Thanks,

Sunday Olutayo


- Original Message -
From: "Craig Ringer" 
To: "SUNDAY A. OLUTAYO" 
Cc: "Jeremy Palmer" , pgsql-general@postgresql.org
Sent: Friday, August 24, 2012 12:48:01 PM
Subject: Re: [GENERAL] Windows SIngle Sign On - LINUX Server

On 08/24/2012 06:10 PM, SUNDAY A. OLUTAYO wrote:
> LDAP will be your best choice for SSO, Ubuntu Linux can authenticate against 
> AD.

I'm not at all convinced by that. Active Directory functions as a
Kerberos KDC. Kerberos provides secure authentication and (unlike LDAP)
single sign-on.

http://technet.microsoft.com/en-us/library/bb742516.aspx

Use Kerberos via GSSAPI. Here's a good starting point by Marcus:

http://www.hagander.net/talks/Deploying%20PostgreSQL%20in%20a%20Windows%20Enterprise.pdf

--
Craig Ringer
This message contains information, which is confidential and may be subject to 
legal privilege. If you are not the intended recipient, you must not peruse, 
use, disseminate, distribute or copy this message. If you have received this 
message in error, please notify us immediately (Phone 0800 665 463 or 
i...@linz.govt.nz) and destroy the original message. LINZ accepts no 
responsibility for changes to this email, or for any attachments, after its 
transmission from LINZ. Thank You.


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

[GENERAL] Does continue in a loop not commit any changes

[Rhys A.D. Stewart]

Greetings all,

Having an issue with the pl/pgsql function below. I may or may not
make an update to a table in the first IF statement. However, whenever
an update is made, if the loop is continued then the update is not
committed.
Is it that whenever a loop is not completed rows aren't written?
Didn't see anything in the docs that would suggest that so I think
that I am missing something very obvious.


Thanks,

Rhys

CREATE OR REPLACE FUNCTION netone.flow_(origin integer)
  RETURNS void AS
$BODY$

DECLARE
lr record;
nextid integer;
nextgeom geometry;
l2 record;

BEGIN
create temporary table if not exists pointhold(id integer) on commit 
drop;
create temporary table if not exists linehold(id integer) on commit 
drop;
RAISE NOTICE 'STARTING AT %', origin;
for lr in SELECT gnid, id,a.geom as ag, b.geom as bg from
netone.points a, netone.lines b where gnid = origin AND
st_intersects(a.geom,b.geom) /*AND id NOT IN (select id from
linehold)*/ LOOP
RAISE NOTICE 'LINE # %', lr.id;
IF st_intersects(st_startpoint(lr.bg),lr.ag) THEN / 
statement
where table may or may no be updated/
raise notice 'reversed';
update netone.lines set geom = st_reverse(geom) where 
id = lr.id;
END IF;
insert into pointhold (id) values (origin);
insert into linehold (id) values (lr.id);
select into l2 gnid, status from netone.points where
st_intersects(lr.bg, geom) AND gnid not in (select id from pointhold);
CONTINUE WHEN  l2.status = 'OPENED'  /*** once this is true the
previous update is not committed ***/

PERFORM netone.flow_(l2.gnid);
END LOOP;
END;

$BODY$
  LANGUAGE plpgsql


-- 
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 Run EnterpriseDB Postgresql Installation

[javad M]

Hi, I solved it. I had created a directory junction in default downloads
location to another directory in another drive. The installer was running
from there and giving this error. I just remembered and though to check it
from normal directory and yup, installer was running and installed fine and
tested okay. So, your installer does not run though directory junction
points in ntfs. There was nothing about this anywhere. Please make note of
this somewhere. Thanks for your time.

On Thu, Aug 23, 2012 at 11:40 PM, javad M  wrote:

> One more thing i forgot to add
> The console - help - about window reads
> Tcl for Windows
> Tcl 8.5.9
> Tk 8.5.9
>
> So i searched and tried downloading and running the latest Tcl available
> from http://www.activestate.com/activetcl
> And what do you know, the same thing as in the screenshot running that tcl
> setup
> Maybe some issue with the tcl in the postgresql setup
> I tried older postgresql setup version like 9.0.9, 9.1.4, x64 setups but
> same issue.
> For some reason the tcl component is creating some issue,
>
> Javad
>
> On Thu, Aug 23, 2012 at 11:32 PM, javad M  wrote:
>
>> i have attached the screenshot. I searched everywhere for any log file.
>> but nothing.
>>
>>
>> On Thu, Aug 23, 2012 at 9:10 PM, Sachin Srivastava <
>> sachin.srivast...@enterprisedb.com> wrote:
>>
>>> Hello,
>>>
>>> Can we get a screenshot? Also you can check for any partial installation
>>> logs in your %TEMP% as install-postgresql.log or
>>> bitrock_installer_.log. Check the %TEMP% of the Administrator as well
>>> (If you dont see any logs in the %TEMP% of the logged in user)
>>>
>>>
>>>
>>> On Thu, Aug 23, 2012 at 8:01 PM, javad M  wrote:
>>>
 Hi, i just formatted my machine and installed fresh win7 x64. Also
 installed VS2012 since i do .net developement. In backend i use postgresql
 so downloaded latest postgresql 9.1.5 installation. But, i am not able to
 install. Upon executing file "postgresql-9.1.5-1-windows.exe" it asks for
 UAC and i say yes then i get a black window and another window named
 console with "(Downloads) 1 % " written in it. I have installed old version
 before and also on many clients but this is first time i am encountering
 this issue. There are also no logs anywhere. Please help, as my development
 is fully stranded because of this issue.

>>>
>>>
>>>
>>> --
>>> Regards,
>>> Sachin Srivastava
>>> EnterpriseDB, India
>>>
>>
>>
>

Re: [GENERAL] run function on server restart

[John D. West]

I have various background processes outside of postgres that need to be
killed and restarted after the server reboots.

 -- John


On Fri, Aug 24, 2012 at 2:29 PM, Kevin Grittner  wrote:

> "John D. West"  wrote:
>
> > Sometimes the server process crashes and restarts, usually when I
> > run some large calculations that eat up all available memory.
>
> You might want to reconfigure to avoid that.
>
> > Is there any way to detect this and run a cleanup routine when it
> > happens?
>
> What is it that are you trying to clean up?
>
> -Kevin
>

Re: [GENERAL] run function on server restart

[Rob Sargent]

On 08/24/2012 03:46 PM, John D. West wrote:

I have various background processes outside of postgres that need to be
killed and restarted after the server reboots.

  -- John


On Fri, Aug 24, 2012 at 2:29 PM, Kevin Grittner
mailto:kevin.gritt...@wicourts.gov>> wrote:

"John D. West" mailto:john.d.w...@asu.edu>> wrote:

 > Sometimes the server process crashes and restarts, usually when I
 > run some large calculations that eat up all available memory.

You might want to reconfigure to avoid that.

 > Is there any way to detect this and run a cleanup routine when it
 > happens?

What is it that are you trying to clean up?

-Kevin


Seems it would be the responsibility of the dependent processes to 
recognize pg went away, and clean themselves up (as only they know how 
to do).





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

[GENERAL] fast-archiver tool, useful for pgsql DB backups

[Mathieu Fenniak]

Hi pgsql-general,

Has anyone else ever noticed how slow it can be to rsync or tar a pgdata
directory with hundreds of thousands or millions of files?  I thought this
could be done faster with a bit of concurrency, so I wrote a little tool
called fast-archiver to do so.  My employer (Replicon) has allowed me to
release this tool under an open source license, so I wanted to share it
with everyone.

fast-archiver is written in Go, and makes uses of Go's awesome concurrency
capabilities to read and write files in parallel.  When you've got lots of
small files, this makes a big throughput improvement.

For a 90GB PostgreSQL database with over 2,000,000 data files,
fast-archiver can create an archive in 27 minutes, as compared to tar in
1hr 23 min.

Piped over an ssh connection, fast-archiver can transfer and write the same
dataset on a gigabit network in 1hr 20min, as compared to rsync taking 3hrs
for the same transfer.

fast-archiver is available at GitHub:
https://github.com/replicon/fast-archiver

I hope this is useful to others. :-)

Mathieu



$ time fast-archiver -c -o /dev/null /db/data
skipping symbolic link /db/data/pg_xlog
1008.92user 663.00system 27:38.27elapsed 100%CPU (0avgtext+0avgdata
24352maxresident)k
0inputs+0outputs (0major+1732minor)pagefaults 0swaps

$ time tar -cf - /db/data | cat > /dev/null
tar: Removing leading `/' from member names
tar: /db/data/base/16408/12445.2: file changed as we read it
tar: /db/data/base/16408/12464: file changed as we read it
32.68user 375.19system 1:23:23elapsed 8%CPU (0avgtext+0avgdata
81744maxresident)k
0inputs+0outputs (0major+5163minor)pagefaults 0swaps

Re: [GENERAL] run function on server restart

[Kevin Grittner]

"John D. West"  wrote:
 
> I have various background processes outside of postgres that need
> to be killed and restarted after the server reboots.
 
All of our applications are coded such that when they have an error
on a database connection, they check for a serialization failure or
a broken connection; if they find either they retry the database
transaction.  That would be my first recommendation.  (Well, second,
after identifying the causes of server crashes and fixing them.)
 
Are you running any monitoring applications which could trigger what
you want?
 
-Kevin


-- 
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] run function on server restart

[John D. West]

I *think* my independent processes are cleaning up in that they supposedly
abort themselves if they lose db connection, but on restart there is a
table of pid's I'd like to (1) make sure all of the processes are really
dead, killing any who aren't (2) reset flags in a table showing the status
of those process, and (3) clear out the pid list so I can restart the
processes.

Apparently the answer to my original question is "no, there is no way to
run a function at postgres startup." The question on monitoring apps
intrigues me, however. What can they do, and what is available?

Thanks!

 -- John


On Fri, Aug 24, 2012 at 3:56 PM, Kevin Grittner  wrote:

> "John D. West"  wrote:
>
> > I have various background processes outside of postgres that need
> > to be killed and restarted after the server reboots.
>
> All of our applications are coded such that when they have an error
> on a database connection, they check for a serialization failure or
> a broken connection; if they find either they retry the database
> transaction.  That would be my first recommendation.  (Well, second,
> after identifying the causes of server crashes and fixing them.)
>
> Are you running any monitoring applications which could trigger what
> you want?
>
> -Kevin
>

Re: [GENERAL] run function on server restart

[Chris Angelico]

On Sat, Aug 25, 2012 at 8:25 AM, John D. West  wrote:
> I *think* my independent processes are cleaning up in that they supposedly
> abort themselves if they lose db connection, but on restart there is a table
> of pid's I'd like to (1) make sure all of the processes are really dead,
> killing any who aren't (2) reset flags in a table showing the status of
> those process, and (3) clear out the pid list so I can restart the
> processes.

Hmm. I wonder is there a way to not use a table for this. What's the
status flagging intended to do? Can it be accomplished with, perhaps,
locks?

ChrisA


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