Re: [GENERAL] call initdb as regular user

[alexondi]

I call initdb with such params

*/opt/PostgreSQL/9.1/bin/initdb -D /projects/data_dir/data -E UTF-8 -U u*ser

after that create pg_log dir when I would create log (but why I must do this
step?)
change hba file and start database server with this command
/*opt/PostgreSQL/9.1/bin/pg_ctl start -w -D /projects/data_dir/data -l
/projects/data_dir/data/pg_log/startup.log*
Server was started but when I try connect to database I see in lgo such
messages
/FATAL: role "postgres" does not exist/
What is this? I use another user?

--
View this message in context: 
http://postgresql.1045698.n5.nabble.com/call-initdb-as-regular-user-tp4712980p4714889.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

-- 
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] call initdb as regular user

[alexondi]

sorry, my mistake when I connect to user I set login to "postgres"

--
View this message in context: 
http://postgresql.1045698.n5.nabble.com/call-initdb-as-regular-user-tp4712980p4714912.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

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

[GENERAL] synchronous_standby_names with '*'

[alexondi]

Hi!
I try create master for replication and in his config I set
synchronous_standby_names = '*'
so I can connect with other slave's (name of this slave I don't know at this
moment) 
But if I try execute some commands (I hung on 'create database') my program
hang and after some time I see
in processes 
... create database waiting for 0/XX
and when I strace to wal I see
...
select ... timeout
getpid() 
select ... timeout
getpid() 
...
and so on.
What so '*' mean in this GUC?

version 9.1beta3 linux x86_64


--
View this message in context: 
http://postgresql.1045698.n5.nabble.com/synchronous-standby-names-with-tp4715117p4715117.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

-- 
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] synchronous_standby_names with '*'

[Fujii Masao]

On Fri, Aug 19, 2011 at 7:06 PM, alexondi  wrote:
> Hi!
> I try create master for replication and in his config I set
> synchronous_standby_names = '*'
> so I can connect with other slave's (name of this slave I don't know at this
> moment)
> But if I try execute some commands (I hung on 'create database') my program
> hang and after some time I see
> in processes
> ... create database waiting for 0/XX
> and when I strace to wal I see
> ...
> select ... timeout
> getpid()
> select ... timeout
> getpid()
> ...
> and so on.
> What so '*' mean in this GUC?

Setting synchronous_standby_names forces the master to perform
synchronous replication. If synchronous_commit is set to 'on',
all the transactions must wait for their WAL to be replicated to
the standby. Since you set synchronous_standby_names to '*',
"create database" hanged, i.e., was waiting for its WAL to be
replicated to the standby.

Only standby whose application_name matches synchronous_standby_names
can run as synchronous standby. '*' matches any application_name, so
'*' means that any standby can run as synchronous one.

Regards,

-- 
Fujii Masao
NIPPON TELEGRAPH AND TELEPHONE CORPORATION
NTT Open Source Software Center

-- 
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] synchronous_standby_names with '*'

[alexondi]

but if I don't have any slave at this time why my command hang?

--
View this message in context: 
http://postgresql.1045698.n5.nabble.com/synchronous-standby-names-with-tp4715117p4715224.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

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

[GENERAL] Getting value of bind variables

[Jayadevan M]

Hell l,
I am trying to debug a query that gives an error. The error in the 
application server log is 
" ERROR: operator does not exist: timestamp without time zone = character 
varying
  Hint: No operator matches the given name and argument type(s). You might 
need to add explicit type casts.
  Position: 1274"
How can I get the value of the $1,$2 etc in the query?
WHERE MST.CMPCOD= $1 
  AND   UPPER  (  CUSINDINF.FAMNAM  )=  UPPER  ($2) 
  AND   UPPER  (  CUSINDINF.INITLS  )=  UPPER  ($3) 
  AND   ( CUSINDINF.MEMDOB ) = ($4) 
  AND MST.CUSNUM <> $5

It looks like the error is for the condition ( CUSINDINF.MEMDOB ) = ($4) , 
memdob being a date of birth (timestamp) column. When I try the query at 
psql with some values, the data is retrieved OK. Is there some logging 
available in PostgreSQL that will tell me what values were actually used?
As of now, I am trying with logging level set to debug5 in postgresql.conf 
 for all logging options. But what is the 'correct' approach?

Regards,
Jayadevan





DISCLAIMER: 

"The information in this e-mail and any attachment is intended only for 
the person to whom it is addressed and may contain confidential and/or 
privileged material. If you have received this e-mail in error, kindly 
contact the sender and destroy all copies of the original communication. 
IBS makes no warranty, express or implied, nor guarantees the accuracy, 
adequacy or completeness of the information contained in this email or any 
attachment and is not liable for any errors, defects, omissions, viruses 
or for resultant loss or damage, if any, direct or indirect."

Re: [GENERAL] synchronous_standby_names with '*'

[Fujii Masao]

On Fri, Aug 19, 2011 at 8:04 PM, alexondi  wrote:
> but if I don't have any slave at this time why my command hang?

Because there is no standby at that time ;)

If synchronous replication is enabled but there is no standby connecting
to the master, a write transaction waits until at least one synchronous
standby has appeared and its WAL has been replicated to it.

Regards,

-- 
Fujii Masao
NIPPON TELEGRAPH AND TELEPHONE CORPORATION
NTT Open Source Software Center

-- 
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] synchronous_standby_names with '*'

[alexondi]

so can I somehow disable|enable synchronous replication at runtime (not only
for session but for the server)?

--
View this message in context: 
http://postgresql.1045698.n5.nabble.com/synchronous-standby-names-with-tp4715117p4715296.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

-- 
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] synchronous_standby_names with '*'

[Fujii Masao]

On Fri, Aug 19, 2011 at 8:39 PM, alexondi  wrote:
> so can I somehow disable|enable synchronous replication at runtime (not only
> for session but for the server)?

Yes.

To disable;
Empty synchronous_standby_names, or set synchronous_commit to "local" or "off".

To enable;
Set synchronous_standby_names or set synchronous_commit to "on".

And then reload the configuration file.

Regards,

-- 
Fujii Masao
NIPPON TELEGRAPH AND TELEPHONE CORPORATION
NTT Open Source Software Center

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

[GENERAL] UUID datatype and GIST index support

[Misa Simic]

Hi,

Is there maybe GIST support patch for UUID datatype?

I do not it it concretly just for UUID column... it is more because of I
need it in EXCLUDE constraint...

CREATE TABLE test_exclude
{
id serial NOT NULL,
guid uuid NOT NULL,
valid_period period NOT NULL,
CONSTRAINT "test_excludepk" PRIMARY KEY (id),
EXCLUDE USING gist (guid WITH =. valid_period WITH &&) --for the same guid,
period must not overlap...

}

Thanks,

Misa

Re: [GENERAL] synchronous_standby_names with '*'

[Alexander Perepelica]

I think if master has not slave's at this time then server must use "local"
mode. But if he has >=1 slave then he must wait until slave accept
transaction. And "synchronous_standby_names" select behaviour about how
master shoud select which slave must be synchronous or not. And when this
GUC is '*' master should use first in the list IMHO.
In docs you (developers) write

PostgreSQL does not provide the system software required to identify a
failure on the primary and notify the standby database server.

But I think you don't have provide software to identify *AND* standby
failure (only notification about this situation's) - and this master without
slave  is this failure (slave don't started)

Re: [GENERAL] synchronous_standby_names with '*'

[Alexander Perepelica]

I think
set synchronous_commit to "off"
Disable synchronous commit only for this session (connect) not for postgres
instance, right?
Maybe I must chnage configuration and call "pg_ctl reload"? to change
deafult behaviour for postgres instance?

2011/8/19 Fujii Masao 

> On Fri, Aug 19, 2011 at 8:39 PM, alexondi  wrote:
> > so can I somehow disable|enable synchronous replication at runtime (not
> only
> > for session but for the server)?
>
> Yes.
>
> To disable;
> Empty synchronous_standby_names, or set synchronous_commit to "local" or
> "off".
>
> To enable;
> Set synchronous_standby_names or set synchronous_commit to "on".
>
> And then reload the configuration file.
>
> Regards,
>
> --
> Fujii Masao
> NIPPON TELEGRAPH AND TELEPHONE CORPORATION
> NTT Open Source Software Center
>

[GENERAL] MySQL -> PostgreSQL conversion issue

[DM]

Hi All,

how to insert mysql (datetime interval) data to postgrres interval datatype.

mysql
 | test_interval   | datetime | YES  | | 1970-01-02 00:00:00 |
 |

psql
 test_interval   | interval   |


any solution for this?

thanks
Deepak

Re: [GENERAL] synchronous_standby_names with '*'

[Simon Riggs]

On Fri, Aug 19, 2011 at 12:04 PM, alexondi  wrote:

> but if I don't have any slave at this time why my command hang?

Setting synchronous_standby_names means "wait until my standby replies".

If you don't have a standby, then you just made a request to wait for
a long time.

Suggest different approach.


-- 
 Simon Riggs   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services

-- 
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] Syncing Data to Production DB Server

[c k]

Yes, sure.
I have already used it. You have to create database connections to both of
your databases. Then create a job to sync data per table as per your needs.
It should not be too hard. Once you job is created then test it for any
errors. If required you have to use data transformations. Once your test
succeed, you can deploy it to java based application server. You can get
more details about it from Talend's documentation. You can also run your job
manually whenever you add data to your demo database. It will be not too
hard.
You can also use stored functions on you demo database to send the updated
data to your main database by using dblink. When ever you update data in
demo db, you can execute these function(s) to update data in main db.

Hope that will be helpful.
Regards,

Chaitany Kulkarni

On Fri, Aug 19, 2011 at 12:37 PM, Adarsh Sharma wrote:

> Thanks CK , I configured Talend but I m not able to understand how we can
> get data synk to our DB servers.
> Any views on that.
>
> c k wrote:
>
>> You can use Talend or Navicat for syncing the data as per your needs
>> without much complexity in writing a data sync application.
>> You have to purchase license for navicat but you can talend for free and
>> it also supports many other database systems.
>>
>> Chaitanya Kulkarni
>>
>> On Fri, Aug 19, 2011 at 10:18 AM, Adarsh Sharma 
>> > adarsh.sharma@orkash.**com >> wrote:
>>
>>Hi Michael,
>>
>>I think you misunderstood my problem.
>>I have a demo system and the data is inserted in this system.
>>
>>Simply I want this newly inserted data to be synk to my production
>>server.
>>Taking pg_dump of cumbersome daily.
>>
>>I reserached & find some proprietary solution but I think there
>>may be other solutions too.
>>
>>
>>Thanks
>>Michael Nolan wrote:
>>
>>>
>>>
>>>On Thu, Aug 18, 2011 at 11:03 PM, Adarsh Sharma
>>>>> >
>>> wrote:
>>>
>>>I want a simple technique through which I update my
>>>production server easily.
>>>
>>>
>>>What I do with a similar sized database is do a pg_dumpall on the
>>>production server and
>>>restore it to the laptop.  Because the production server is
>>>around 950 miles from my office, it usually takes me longer to
>>>copy the dumpall file across the Internet than it does to restore
>>>it on the laptop.
>>>
>>>I do this about twice a month.
>>>
>>>I find having a test database that is a week or two out of date
>>>doesn't affect most development work.  In fact, being able to
>>>restore the test database to a known state repeatedly has come in
>>>handy for testing some scenarios.  Your situation may be
>>>different.  --
>>>Mike Nolan
>>>
>>
>>
>>
>

Re: [GENERAL] [ADMIN] help in postgresql

[c k]

Just use postgresql wiki, postgresql documentation, and few sites who
compare postgresql and oracle.
Both are good database systems, postgresql is open source and free, runs on
most platforms, easy to install and manage than oracle, supports very large
database.

Chaitany Kulkarni

On Thu, Aug 18, 2011 at 3:17 PM, Karuna Karpe
wrote:

> Hello sir,
>
> I am fresher for working as DB-Admin and also beginner for
> postgresql database.  I have bit of knowledge about Oracle.
> So, I have some of the questions about postgres. i.e. Is postgresql store
> huge amount of data like oracle? Most organizations use oracle database, why
> use postgresql? and so on. So, please Can you explain me the difference
> between oracle and postgres? How the postgresql is beneficial then oracle
> and other databases?
> Please help me to being familiar with postgresql.
>
>
>
> Regards,
> karuna karpe.
>

Re: [GENERAL] MySQL -> PostgreSQL conversion issue

[Andy Colson]

On 8/19/2011 7:50 AM, DM wrote:

Hi All,

how to insert mysql (datetime interval) data to postgrres interval datatype.

mysql
  | test_interval   | datetime | YES  | | 1970-01-02 00:00:00 |
|

psql
  test_interval   | interval   |


any solution for this?

thanks
Deepak


Well.. What is the interval in mysql?  Is that supposed to represent + 
interval '1970 years' + interval '1 month' + interval '2 days'?


In PG you probably have a few options.  One, store it as a date, and use 
dateAdd() functions.  Two, store it as text and set the value's to 
things like '1 hour', '2 years', '70 years 1 month 2 days 4 hours 20 
minutes', etc


The calc like 'select someDate + interval yourNewField'

Of course... I have not actually tried any of this :-)

-Andy

--
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] Getting value of bind variables

[Andy Colson]

On 8/19/2011 6:19 AM, Jayadevan M wrote:

Hell l,
I am trying to debug a query that gives an error. The error in the
application server log is
" ERROR: operator does not exist: timestamp without time zone =
character varying
Hint: No operator matches the given name and argument type(s). You might
need to add explicit type casts.
Position: 1274"
How can I get the value of the $1,$2 etc in the query?
WHERE MST.CMPCOD= $1
AND UPPER ( CUSINDINF.FAMNAM )= UPPER ($2)
AND UPPER ( CUSINDINF.INITLS )= UPPER ($3)
AND ( CUSINDINF.MEMDOB ) = ($4)
AND MST.CUSNUM <> $5

It looks like the error is for the condition ( CUSINDINF.MEMDOB ) = ($4)
, memdob being a date of birth (timestamp) column. When I try the query
at psql with some values, the data is retrieved OK. Is there some
logging available in PostgreSQL that will tell me what values were
actually used?
As of now, I am trying with logging level set to debug5 in
postgresql.conf for all logging options. But what is the 'correct'
approach?

Regards,
Jayadevan



Not sure what the exact setting is, try:

log_statement = 'all'

I've never set the log_min_messages or log_min_error_statement, and I'm 
pretty sure I've seen values in my logs.



-Andy

--
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] Getting value of bind variables

[Adrian Klaver]

On Friday, August 19, 2011 6:55:39 am Andy Colson wrote:
> > As of now, I am trying with logging level set to debug5 in
> > postgresql.conf for all logging options. But what is the 'correct'
> > approach?
> > 
> > Regards,
> > Jayadevan
> 
> Not sure what the exact setting is, try:
> 
> log_statement = 'all'
> 
> I've never set the log_min_messages or log_min_error_statement, and I'm
> pretty sure I've seen values in my logs.

If I remember correctly this is version specific, though I cannot remember what 
version the actual variables where made visible:(

> 
> 
> -Andy

-- 
Adrian Klaver
adrian.kla...@gmail.com

-- 
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] Getting value of bind variables

[Tom Lane]

Jayadevan M  writes:
> I am trying to debug a query that gives an error. The error in the 
> application server log is 
> " ERROR: operator does not exist: timestamp without time zone = character 
> varying

> It looks like the error is for the condition ( CUSINDINF.MEMDOB ) = ($4) , 
> memdob being a date of birth (timestamp) column. When I try the query at 
> psql with some values, the data is retrieved OK. Is there some logging 
> available in PostgreSQL that will tell me what values were actually used?

Yes, if you're using a reasonably recent version of Postgres ---
log_statements should provide that information.  However, it's 100%
irrelevant to this problem what the specific value is.  The problem is
that the application is declaring the *type* of $4 as varchar rather
than something appropriate.

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] Getting value of bind variables

[Andy Colson]

On 8/19/2011 9:01 AM, Adrian Klaver wrote:

On Friday, August 19, 2011 6:55:39 am Andy Colson wrote:

As of now, I am trying with logging level set to debug5 in
postgresql.conf for all logging options. But what is the 'correct'
approach?

Regards,
Jayadevan


Not sure what the exact setting is, try:

log_statement = 'all'

I've never set the log_min_messages or log_min_error_statement, and I'm
pretty sure I've seen values in my logs.


If I remember correctly this is version specific, though I cannot remember what
version the actual variables where made visible:(



Ah, yeah, you are probably right.

I can confirm in PG 9, with just

>> log_statement = 'all'

I see things like:

DETAIL:  parameters: $1 = '%oak%'

in my log.

-Andy

--
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] Dump a database excluding one table DATA?

[Adrian Klaver]

On Thursday, August 18, 2011 3:25:59 pm Dmitry Koterov wrote:
> Mmm, --disable-triggers is not surely enough - we also have RULEs and (much
> worse) INDEXes.
> 
> If we create all indices and then restore all data, it is MUCH SLOWER than
> restore the data first and then - create all indices.
> So I think that there is no work-around really...

Yes there is. Do the schema only dump of the table in question, comment out the 
Indexes and rules you don't want. Load the data. Go back to the schema dump 
comment out the table and uncomment the Indexes/rules,etc . 
Or
Write a script that does the same thing automatically. 


> 
> I propose to include an option to pg_dump to skip several tables data
> restoration. :-)
> 

I thought you where looking to exclude only one table. As you found out the 
dependency issues for one table can be a problem. Doing more than one makes the 
process even more brittle.

-- 
Adrian Klaver
adrian.kla...@gmail.com

-- 
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 linux uid in pg-psql

[Gauthier, Dave]

Is there a way to get the linux idsid of a user, even for a remote network 
connection?

If not, is there a way to capture this somehow when the original connection is 
made and maybe stuff it in a temp table or something using whatever means (a 
trigger-like mechanism? ) ?   Is there a script/function that gets run whenever 
a connection is made, something I might have access to, in which I could 
piggy-back something like this?  What I'm talking about is basically grabbing 
and storing $USER for me to read later on with a stored proc.

I could write a pg-perlu to get this, but I suspect it won't give me the 
original user when there's a remote connect.

Thanks for any suggestions !

Re: [GENERAL] altering foreign key without a table scan

[Vincent de Phily]

On Thursday 18 August 2011 13:08:18 Jerry Sievers wrote:
> Vincent de Phily  writes:
> > The thing is, I know there is no violation by existing data, because of
> > the existing fkey. So locking and scaning the table to add the
> > "duplicate" fkey is> 
> > not necessary. In a sense, I'm looking for :
> >> ALTER TABLE foo ALTER CONSTRAINT foo_barid_fkey ON UPDATE RESTRICT;
> > 
> > I'm guessing/wishfull-thinking that some hackery with the system catalog
> > could emulate that ?
> > 
> > I'm currently using postgres 8.3 (we want to upgrade, but it's hard to
> > schedule).
> 
> Two things first...
> 
> 1. I assume this is same for 8.3
> 2. Someone from Hackers best to answer if this is safe on live system
>or might require at least a restart.
> 
> Your 2 catalog fields of interest are;
> pg_constraint.(confupdtype|confdeltype)
> 
> Changing those for the relevant FKs should satisfy your needs.  I am
> not aware of those field values being duplicated anywhere.

Thanks for your answer. Experimenting a bit, those columns seem to have only a 
cosmetic impact, meaning that "\d" will show the schema you expect, but the 
behaviour remains unchanged (even after restarting postgres).

Digging further however, I found that pg_triggers can be used for my means :


CREATE TABLE tref(id INTEGER PRIMARY KEY);
CREATE TABLE t(id INTEGER PRIMARY KEY,   
   refid INTEGER REFERENCES tref(id) ON DELETE RESTRICT);
INSERT INTO tref(id) VALUES (1),(2),(3);
INSERT INTO t(id, refid) VALUES (1,1),(2,NULL),(3,1),(4,2);

-- Cosmetic part: fkey looks updated but behaves the same (DELETE will fail)

  
UPDATE pg_constraint SET confdeltype ='c' WHERE conname='t_refid_fkey'; 
\d t
DELETE FROM tref WHERE id=1;

-- Functional part: DELETE will now work (after opening a new connection)   

  
UPDATE pg_trigger SET tgfoid=(SELECT oid FROM pg_proc
  WHERE proname ='RI_FKey_cascade_del')
   WHERE tgconstrname='t_refid_fkey'
  AND tgfoid=(SELECT oid FROM pg_proc
  WHERE proname ='RI_FKey_restrict_del');
\c
DELETE FROM tref WHERE id=1;


> Strongly suggest you approach this with caution, as is standard
> advice regarding any manual catalog fiddling.

Of course. The psql script above works in my tests, but I could easily have 
missed a side-effect that will comme back to bite me at the worst moment. 
Unless someone can confirm that there are no hidden gotcha with this method, 
I'll probably wait until our migration to PG9.0 to do those schema changes.


BTW, if anybody picks up the "ALTER CONSTRAINT" feature (low hanging fruit ?) 
for the next postgres release, I'll be happy to ship them their 
$FAVORITE_REWARD_BEVERAGE in the post :)

-- 
Vincent de Phily
Mobile Devices
+33 (0) 142 119 325
+353 (0) 85 710 6320 

Warning
This message (and any associated files) is intended only for the use of its
intended recipient and may contain information that is confidential, subject
to copyright or constitutes a trade secret. If you are not the intended
recipient you are hereby notified that any dissemination, copying or
distribution of this message, or files associated with this message, is
strictly prohibited. If you have received this message in error, please
notify us immediately by replying to the message and deleting it from your
computer. Any views or opinions presented are solely those of the author
vincent.deph...@mobile-devices.fr and do not necessarily represent those of 
the
company. Although the company has taken reasonable precautions to ensure no
viruses are present in this email, the company cannot accept responsibility
for any loss or damage arising from the use of this email or attachments.

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

Re: [GENERAL] Need linux uid in pg-psql

[Alan Hodgson]

On August 19, 2011 07:01:33 AM Gauthier, Dave wrote:
> Is there a way to get the linux idsid of a user, even for a remote network
> connection?
> 
> I could write a pg-perlu to get this, but I suspect it won't give me the
> original user when there's a remote connect.
> 
> Thanks for any suggestions !

There's an identd protocol for this. It's not commonly used anymore, and when 
present tends to deliberately obscure the results. 

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

[GENERAL] date_trunc - not sure what is wrong, but it confuses me.

[dexdyne]

I'm tearing my hair out trying to understand time zones.

I want to use date_trunc to find the start and end of last day, week, month
at a remote site.

I looked at the date_trunc docs and they say

function   date_trunc(text, timestamp)   
Return Type  timestamp   
Description   Truncate to specified precision; 
   see also Section 9.9.2
Example   date_trunc('hour', timestamp '2001-02-16 20:38:40') 
Result 2001-02-16 20:00:00

Now I took that to mean that the function takes a parameter of type
"timestamp without time zone", since the word timestamp along is these days
a synonym for that. 

however, if I put this query into pgAdmin

   SELECT date_trunc( 'WEEK' , (timestamp withouttime zone '2011-06-20
10:30 US/Hawaii') 
  
   results in  date_trunc --- timestamp without time zone "2011-06-20
00:00:00"

but
   SELECT date_trunc( 'WEEK' , (timestamp with   time zone '2011-06-20
10:30 US/Hawaii') 
   
   results in  date_trunc --- timestamp withtime zone "2011-06-20
00:00:00+01"

So it looks as if date_trunc is taking either type and returning a value of
the same type.
A perfectly reasonable thing to do - but does that correspond with the
documentation?




--
View this message in context: 
http://postgresql.1045698.n5.nabble.com/date-trunc-not-sure-what-is-wrong-but-it-confuses-me-tp4716052p4716052.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

-- 
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] altering foreign key without a table scan

[Tom Lane]

Vincent de Phily  writes:
> On Thursday 18 August 2011 13:08:18 Jerry Sievers wrote:
>> Your 2 catalog fields of interest are;
>> pg_constraint.(confupdtype|confdeltype)
>> 
>> Changing those for the relevant FKs should satisfy your needs.  I am
>> not aware of those field values being duplicated anywhere.

> Thanks for your answer. Experimenting a bit, those columns seem to have only 
> a 
> cosmetic impact, meaning that "\d" will show the schema you expect, but the 
> behaviour remains unchanged (even after restarting postgres).

> Digging further however, I found that pg_triggers can be used for my means :

IIRC, there are fields of pg_constraint that are copied into the
pg_trigger rows for the supporting triggers, so as to save one catalog
lookup at run time.  If you diddle one manually, you'd better diddle
both.

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] Need linux uid in pg-psql

[Chris Travers]

On Fri, Aug 19, 2011 at 8:40 AM, Alan Hodgson  wrote:
> On August 19, 2011 07:01:33 AM Gauthier, Dave wrote:
>> Is there a way to get the linux idsid of a user, even for a remote network
>> connection?
>>
>> I could write a pg-perlu to get this, but I suspect it won't give me the
>> original user when there's a remote connect.
>>
>> Thanks for any suggestions !
>
> There's an identd protocol for this. It's not commonly used anymore, and when
> present tends to deliberately obscure the results.
>
Couldn't you use client ssl certs for authentication and encode the
data there in the cert?

Best Wishes,
Chris Travers

-- 
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] Need linux uid in pg-psql

[Tom Lane]

Alan Hodgson  writes:
> On August 19, 2011 07:01:33 AM Gauthier, Dave wrote:
>> Is there a way to get the linux idsid of a user, even for a remote network
>> connection?

> There's an identd protocol for this. It's not commonly used anymore, and when
> present tends to deliberately obscure the results. 

Not to mention that it's trivially faked, if the user has root on his
own machine.

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] date_trunc - not sure what is wrong, but it confuses me.

[Tom Lane]

dexdyne  writes:
> So it looks as if date_trunc is taking either type and returning a value of
> the same type.

Yes, as \df would have told you.

> A perfectly reasonable thing to do - but does that correspond with the
> documentation?

Could be a bit more specific, perhaps.

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] Need linux uid in pg-psql

[Gauthier, Dave]

Not too worried about nefarious id faking in this environment.  
How does one use "identd" in an unobscured way?

-Original Message-
From: pgsql-general-ow...@postgresql.org 
[mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Tom Lane
Sent: Friday, August 19, 2011 11:55 AM
To: Alan Hodgson
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Need linux uid in pg-psql 

Alan Hodgson  writes:
> On August 19, 2011 07:01:33 AM Gauthier, Dave wrote:
>> Is there a way to get the linux idsid of a user, even for a remote network
>> connection?

> There's an identd protocol for this. It's not commonly used anymore, and when
> present tends to deliberately obscure the results. 

Not to mention that it's trivially faked, if the user has root on his
own machine.

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

-- 
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] altering foreign key without a table scan

[Vincent de Phily]

On Friday 19 August 2011 11:52:50 Tom Lane wrote:
> Vincent de Phily  writes:
> > Thanks for your answer. Experimenting a bit, those columns seem to have
> > only a cosmetic impact, meaning that "\d" will show the schema you
> > expect, but the behaviour remains unchanged (even after restarting
> > postgres).
> 
> > Digging further however, I found that pg_triggers can be used for my means
> > :
> IIRC, there are fields of pg_constraint that are copied into the
> pg_trigger rows for the supporting triggers, so as to save one catalog
> lookup at run time.  If you diddle one manually, you'd better diddle
> both.

Some relid values are indeed duplicated in pg_constraint and pg_trigger, but 
it doesn't look like I need to fiddle with those ?

I'm only touching pg_trigger.tgfoid and pg_constraint.confdeltype/confupdtype 
(which indeed seem to say the same thing in a different way). Do you know if 
there is something else I've missed ?

Thanks.

-- 
Vincent de Phily

-- 
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] Need linux uid in pg-psql

[Tom Lane]

"Gauthier, Dave"  writes:
> Not too worried about nefarious id faking in this environment.  
> How does one use "identd" in an unobscured way?

There's a command-line switch for the identd daemon, on most machines,
that tells it whether to send hashed or plaintext responses.

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] altering foreign key without a table scan

[Tom Lane]

Vincent de Phily  writes:
> On Friday 19 August 2011 11:52:50 Tom Lane wrote:
>> IIRC, there are fields of pg_constraint that are copied into the
>> pg_trigger rows for the supporting triggers, so as to save one catalog
>> lookup at run time.  If you diddle one manually, you'd better diddle
>> both.

> Some relid values are indeed duplicated in pg_constraint and pg_trigger, but 
> it doesn't look like I need to fiddle with those ?

> I'm only touching pg_trigger.tgfoid and pg_constraint.confdeltype/confupdtype 
> (which indeed seem to say the same thing in a different way). Do you know if 
> there is something else I've missed ?

Yeah, that seems to be it except for the deferrable/deferred fields,
which match up in the obvious way.  I had been thinking the RI triggers
avoided doing a lookup in pg_constraint, but that was mistaken.  (I
think we used to store all that info in tgargs, but we evidently don't
anymore.)

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] altering foreign key without a table scan

[Vincent de Phily]

On Friday 19 August 2011 12:55:01 Tom Lane wrote:
> Vincent de Phily  writes:
> > On Friday 19 August 2011 11:52:50 Tom Lane wrote:
> >> IIRC, there are fields of pg_constraint that are copied into the
> >> pg_trigger rows for the supporting triggers, so as to save one catalog
> >> lookup at run time.  If you diddle one manually, you'd better diddle
> >> both.
> > 
> > Some relid values are indeed duplicated in pg_constraint and pg_trigger,
> > but it doesn't look like I need to fiddle with those ?
> > 
> > I'm only touching pg_trigger.tgfoid and
> > pg_constraint.confdeltype/confupdtype (which indeed seem to say the
> > same thing in a different way). Do you know if there is something else
> > I've missed ?
> 
> Yeah, that seems to be it except for the deferrable/deferred fields,
> which match up in the obvious way.  I had been thinking the RI triggers
> avoided doing a lookup in pg_constraint, but that was mistaken.  (I
> think we used to store all that info in tgargs, but we evidently don't
> anymore.)

Thanks, I'll look into applying those next week then. If I never get back to 
the list about it, it'll mean that it worked without issues :)

-- 
Vincent de Phily


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

[GENERAL] Listen/notify and ODBC

[Mark Morgan Lloyd]

In early 2009 I asked whether there was a hack to allow PostgreSQL's 
listen/notify commands via ODBC, some while later I believe that 
somebody sent me example code encoded as base64 which erroneously got 
deleted as spam.


Google suggests that several other people have asked about this over the 
years: if anybody still has this example do you think you could post a 
URL where it might be found?


My apologies for posting this here as well as in the ODBC ML.

--
Mark Morgan Lloyd
markMLl .AT. telemetry.co .DOT. uk

[Opinions above are the author's, not those of his employers or colleagues]

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

[GENERAL] Dump database roles for full restoration

[Diego Augusto Molina]

Short description:
* Need: migration of database, with roles relevant to it.
* "From" server: pg 8.1; no superuser access.
* "To" server: pg 8.4; full access.
* Database in question: depends on few roles, mainly group roles used
to set permission; there are other roles (login roles) which are
members of these and are intended for the users of the system.
* Proposed solution: migrate the database and reset users passwords.

Long description:
I've been searching a way to achieve this. Having not found it, I
proposed the following solution:

1) Get the roles which are explicitly part of the database through
ownership or acl.
2) Get the roles which descend into the membership graph (members of
roles in (1) plus the members of these and so on).
3) Dump them all (which includes the statements "CREATE ROLE", "ALTER
ROLE" and "GRANT"'s referred to role membership).

Why not include the upper part of the graph (parent roles)? well,
unless any of these are superusers any other permission is irrelevant
for the purpose of that single database. In fact, that's a TODO which
is not needed for my particular case.
I wrote two functions for this: the first one is a privileged one and
the second is not. I did the second because I can't access to the
passwords of the users in the server I'm migrating and asking the
admins to do this is such a pain. After the restoration I will reset
the application users' passwords and securely transmit them to them.
Additionally, the functions can dump the roles of any number of
databases in the cluster.
In the attachment you'll find two queries which are later combined and
slightly modified to compose the functions. One of the queries is a
"WITH RECURSIVE ..." query, so the PostgreSQL version should be 8.3 or
above I guess. Given the case that the cluster which I'm migrating
from is 8.1, I had to take a workaround in order for this to work:

1) Dump the needed catalog tables: pg_auth_members, pg_authid for
privileged role OR pg_roles for unprivileged role (which is in fact a
view), pg_database and pg_shdepend.
2) Restore the tables in some user-schema of some database in the 8.4 cluster.
3) Modify the functions to query the specified schema instead of pg_catalog.

I provide this code in the hope that it will be useful to someone, but
I also expect some comments and/or corrections.
NOTICE: the privileged function was already successfully tested in a
production environment between two 8.4 servers.

-- 
Diego Augusto Molina
diegoaugustomol...@gmail.com

ES: Por favor, evite adjuntar documentos de Microsoft Office. Serán
desestimados.
EN: Please, avoid attaching Microsoft Office documents. They shall be discarded.
LINK: http://www.gnu.org/philosophy/no-word-attachments.html
  -- ES: Consulta que lista los oid de los roles de los cuales depende la base de datos, así como los roles que dependen de éstos primeros.
  -- EN: Query that lists the oids of the roles in which the database deppends on as well as those roles which descend from them.
WITH RECURSIVE deps(rol) AS (
  SELECT DISTINCT roles.oid
FROM
  pg_catalog.pg_database db INNER JOIN
  pg_catalog.pg_shdepend dep ON (db.oid = dep.dbid) INNER JOIN
  pg_catalog.pg_roles roles ON (dep.refobjid = roles.oid)
WHERE
  dep.refclassid = 'pg_catalog.pg_authid'::REGCLASS AND
  ARRAY[db.datname] <@ $1
  UNION ALL
  SELECT am.member AS rol
FROM
  pg_catalog.pg_auth_members am INNER JOIN
  deps ON (am.roleid = deps.rol)
)
SELECT * FROM deps

  -- ES: La siguiente consulta genera el dump de los roles. La columna 'orden' establece el orden en que deben ejecutarse las sentencias.
  -- EN: This query generates the dump of the roles. The column 'orden' sets the order in which the sentencies are to be executed.
SELECT 1::SMALLINT AS orden, 'CREATE ROLE "' || rolname || '" '
CASE WHEN NOT rolsuper  THEN 'NO' ELSE '' END || 'SUPERUSER ' ||-- 'NOSUPERUSER ' ||
CASE WHEN NOT rolinheritTHEN 'NO' ELSE '' END || 'INHERIT ' ||
CASE WHEN NOT rolcreaterole THEN 'NO' ELSE '' END || 'CREATEROLE ' ||   -- 'NOCREATEROLE ' ||
CASE WHEN NOT rolcreatedb   THEN 'NO' ELSE '' END || 'CREATEDB ' || -- 'NOCREATEDB ' ||
CASE WHEN NOT rolcanlogin   THEN 'NO' ELSE '' END || 'LOGIN ' ||
'CONNECTION LIMIT ' || rolconnlimit ||
CASE WHEN rolvaliduntil IS NULL THEN '' ELSE E' VALID UNTIL \'' ||  rolvaliduntil::text || E'\'' END ||
';' AS sentencia
  FROM pg_catalog.pg_roles
  --WHERE ARRAY[oid] <@ $1
  --WHERE oid IN ()
UNION
SELECT 2::SMALLINT AS orden, 'ALTER ROLE "' || rolname || '" SET ' || array_to_string(
rolconfig, ';ALTER ROLE "' || rolname || '" SET '
) || ';' AS sentencia
  FROM pg_catalog.pg_roles
  WHERE rolconfig IS NOT NULL
  --AND ARRAY[oid] <@ $1
  --AND oid IN ()
UNION
SELECT 2::SMALLINT AS orden, 'GRANT "' || rol.rolname || '" TO "' || member.rolname || '"' ||
CASE WHEN am.admin_option THEN 'WITH ADMIN OPTION' ELSE '' END ||
';' AS sentencia
  FROM
pg_catalog.pg_au

Re: [GENERAL] MySQL -> PostgreSQL conversion issue

[Alban Hertroys]

On 19 Aug 2011, at 14:50, DM wrote:

> Hi All,
> 
> how to insert mysql (datetime interval) data to postgrres interval datatype.
> 
> mysql
>  | test_interval   | datetime | YES  | | 1970-01-02 00:00:00 |
> |
> 
> psql
>  test_interval   | interval   |
> 
> 
> any solution for this?

I think you want something like this?:

development=> select '1970-01-02 00:00:00'::timestamp without time zone - 
'1970-01-01 00:00:00'::timestamp without time zone;
 ?column?
 --
 1 day(1 row)

Midnight 1970-01-01 being the baseline for their calculations is a bit of a 
guess, but that seems to make sense with the example you gave.
You probably don't want postgres to be smart with your client's time zone 
settings, hence the "without time zone".

Alban Hertroys

--
Screwing up is an excellent way to attach something to the ceiling.


!DSPAM:737,4e4eadd712091047328385!



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

[GENERAL] A questions on planner choices

[Edoardo Panfili]

I apologize for my english and... also for the explanation perhaps not 
very clear.
I have some doubt regarding the planner choice for my query, usually it 
does a very good job and I would prefer to leave free the planner but 
with this query I have some doubt:


I use tree tables, cartellino with 2 indexes
"cartellino_punto_geom_4326" gist (the_geom)
"specimen_idspecie" btree (idspecie)
A view named specienomi with an index on specienomi.nome
Postgres 8.4.8 with postgis 1.5.3
I can post a complete explain for each query.

This is the original query
SELECT specienomi.nome, cartellino.cont_nome,
ST_AsGML(cartellino.the_geom)
FROM cartellino, specienomi, confini_regioni
WHERE confini_regioni.regione='UMBRIA'
AND specienomi.nome like 'Quercus%'
AND cartellino.idspecie=specienomi.id
AND ST_Intersects(cartellino.the_geom,confini_regioni.the_geom4326);
it tooks 4481.933 ms
the planner does ((cartellino join confini_regioni) join specienomi) but 
I think I want to try another way.



A very big enhancement with:
WITH temp_que AS (
SELECT specienomi.nome AS nome,
cartellino.cont_nome AS cont_nome,
cartellino.id AS id, the_geom
FROM cartellino, specienomi
WHERE specienomi.nome like 'Quercus %'
AND cartellino.idspecie=specienomi.id
)
SELECT temp_que.nome, temp_que.cont_nome, ST_AsGML(temp_que.the_geom)
FROM temp_que, confini_regioni
WHERE confini_regioni.regione='UMBRIA'
AND ST_Intersects(temp_que.the_geom,confini_regioni.the_geom4326);
The time is 45.026 ms

the question is: I am missing some index? or ST_Intersects behaves in a 
way that i don't understand?




after re-reading the manual I did some other try:

set from_collapse_limit=1;
SELECT temp_que.nome, temp_que.cont_nome, ST_AsGML(temp_que.the_geom)
FROM confini_regioni,
(SELECT specienomi.nome AS nome,
cartellino.cont_nome AS cont_nome,
cartellino.id AS id, the_geom
 FROM cartellino, specienomi
 WHERE specienomi.nome like 'Quercus %'
AND cartellino.idspecie=specienomi.id
) AS temp_que
WHERE confini_regioni.regione='UMBRIA'
AND ST_Intersects(temp_que.the_geom,confini_regioni.the_geom4326)
ORDER BY temp_que.id;

works fine 50.126 ms


set join_collapse_limit=1;
SELECT specienomi.nome, ST_AsGML(cartellino.the_geom)
FROM confini_regioni full JOIN (
cartellino full JOIN specienomi ON
(cartellino.idspecie=specienomi.id)) ON
ST_Intersects(cartellino.the_geom,confini_regioni.the_geom4326)
WHERE confini_regioni.regione='UMBRIA'
AND specienomi.nome like 'Quercus%'

is slow: 5750.499 ms
and
NOTICE:  LWGEOM_gist_joinsel called with incorrect join type

thank you
Edoardo



[1] Plan for the firts query
---
 Sort  (cost=20.45..20.46 rows=1 width=931) (actual 
time=4457.775..4457.786 rows=76 loops=1)

   Sort Key: cartellino.id
   Sort Method:  quicksort  Memory: 74kB
   ->  Hash Join  (cost=8.32..20.44 rows=1 width=931) (actual 
time=243.679..4457.658 rows=76 loops=1)

 Hash Cond: (cartellino.idspecie = principale.id)
 ->  Nested Loop  (cost=0.00..9.81 rows=614 width=886) (actual 
time=4.094..4439.024 rows=18370 loops=1)
   Join Filter: _st_intersects(cartellino.the_geom, 
confini_regioni.the_geom4326)
   ->  Seq Scan on confini_regioni  (cost=0.00..1.25 rows=1 
width=1473036) (actual time=0.017..0.021 rows=1 loops=1)

 Filter: ((regione)::text = 'UMBRIA'::text)
   ->  Index Scan using cartellino_punto_geom_4326 on 
cartellino  (cost=0.00..8.30 rows=1 width=886) (actual 
time=0.059..94.148 rows=32200 loops=1)
 Index Cond: (cartellino.the_geom && 
confini_regioni.the_geom4326)
 ->  Hash  (cost=8.28..8.28 rows=3 width=57) (actual 
time=0.392..0.392 rows=74 loops=1)
   ->  Index Scan using i_specie_nome_specie_like on specie 
principale  (cost=0.01..8.28 rows=3 width=57) (actual time=0.034..0.348 
rows=74 loops=1)
 Index Cond: ((esterna_nome(ibrido, proparte, 
(genere)::text, [...]  (cultivar)::text) ~>=~ 'Quercus'::text) AND 
(esterna_nome(ibrido, proparte, (genere)::text, [...] (cultivar)::text) 
~<~ 'Quercut'::text))
 Filter: (esterna_nome(ibrido, proparte, 
(genere)::text, [...] (cultivar)::text) ~~ 'Quercus%'::text)

 Total runtime: 4481.933 ms

--
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] A questions on planner choices

[Scott Marlowe]

On Fri, Aug 19, 2011 at 1:05 PM, Edoardo Panfili  wrote:
> [1] Plan for the firts query
> ---
>  Sort  (cost=20.45..20.46 rows=1 width=931) (actual time=4457.775..4457.786
> rows=76 loops=1)
>   Sort Key: cartellino.id
>   Sort Method:  quicksort  Memory: 74kB
>   ->  Hash Join  (cost=8.32..20.44 rows=1 width=931) (actual
> time=243.679..4457.658 rows=76 loops=1)
>         Hash Cond: (cartellino.idspecie = principale.id)
>         ->  Nested Loop  (cost=0.00..9.81 rows=614 width=886) (actual
> time=4.094..4439.024 rows=18370 loops=1)

The row estimate here is off by a factor of 30 or so.  In this case a
different join method would likely work better.   It might be that
cranking up stats for the columns involved will help, but if that
doesn't change the estimates then we might need to look elsewhere.

What's your work_mem and random_page_cost?

-- 
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] A questions on planner choices

[Edoardo Panfili]

Il 19/08/11 22:15, Scott Marlowe ha scritto:

On Fri, Aug 19, 2011 at 1:05 PM, Edoardo Panfili  wrote:

[1] Plan for the firts query
---
  Sort  (cost=20.45..20.46 rows=1 width=931) (actual time=4457.775..4457.786
rows=76 loops=1)
   Sort Key: cartellino.id
   Sort Method:  quicksort  Memory: 74kB
   ->Hash Join  (cost=8.32..20.44 rows=1 width=931) (actual
time=243.679..4457.658 rows=76 loops=1)
 Hash Cond: (cartellino.idspecie = principale.id)
 ->Nested Loop  (cost=0.00..9.81 rows=614 width=886) (actual
time=4.094..4439.024 rows=18370 loops=1)


The row estimate here is off by a factor of 30 or so.  In this case a
different join method would likely work better.   It might be that
cranking up stats for the columns involved will help, but if that
doesn't change the estimates then we might need to look elsewhere.

What's your work_mem and random_page_cost?

 work_mem = 1MB
 random_page_cost = 4

I am using an SSD but the production system uses a standard hard disk.

I did a try also with
set default_statistics_target=1;
vacuum analyze cartellino;
vacuum analyze specie; -- the base table for specienomi
vacuum analyze confini_regioni;

but is always 4617.023 ms

Edoardo



--
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] Dump a database excluding one table DATA?

[Dimitri Fontaine]

Dmitry Koterov  writes:
> Is there any way (or hack) to dump the whole database, but to exclude the
> DATA from a table within this dump? (DDL of the table should not be
> excluded: after restoring the data the excluded table should look "empty".)

The pg_staging tool allows you to do that quite easily, once you've done
the initial setup (involved, but nothing fancy).

  https://github.com/dimitri/pg_staging
  http://tapoueh.org/pgsql/pgstaging.html

If you want to exclude the table from the dumps though (data loss issues
are looking at you), what I would advice is create a dedicated schema
and skipping the whole schema at dump time, using the following option:

--exclude-schema=schema
Do   not  dump   any   schemas   matching  the   schema
pattern. The  pattern is  interpreted according  to the
same rules as  for -n.  -N can be given  more than once
to exclude schemas matching any of several patterns.

Regards,
-- 
Dimitri Fontaine
http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support

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

[GENERAL] array_agg problem

[TJ O'Donnell]

array_agg says it can take any type of arg, but this seems not to work
when the arg in an integer array.

create temp table x(name text, val integer[]);
insert into x values('a',array[1,2,3]);
insert into x values('b',array[3,4,5]);
select * from x;
select max(val), min(val) from x;
select array_agg(val) from x;

Well, everything works except the last statement.  In 8.4 I get
ERROR:  could not find array type for data type integer[]

I was hoping for [1,2,3,4,5] or at least [1,2,3,3,4,5] or even [[1,2,3],[3,4,5]]
Am I not understanding something?

Thanks,
TJ O'Donnell

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

[Merlin Moncure]

On Fri, Aug 19, 2011 at 4:22 PM, TJ O'Donnell  wrote:
> array_agg says it can take any type of arg, but this seems not to work
> when the arg in an integer array.
>
> create temp table x(name text, val integer[]);
> insert into x values('a',array[1,2,3]);
> insert into x values('b',array[3,4,5]);
> select * from x;
> select max(val), min(val) from x;
> select array_agg(val) from x;
>
> Well, everything works except the last statement.  In 8.4 I get
> ERROR:  could not find array type for data type integer[]
>
> I was hoping for [1,2,3,4,5] or at least [1,2,3,3,4,5] or even 
> [[1,2,3],[3,4,5]]
> Am I not understanding something?

yeah basically -- it's a common misunderstanding.  array_agg converts
elements to arrays, but not arrays to array of dimension + 1.  you
could do this:

select array(select unnest(val) from x);

to get [1,2,3,3,4,5].

Getting [[1,2,3],[3,4,5]] is a little harder:
create aggregate array_stack(int[])
(
  sfunc=array_cat,
  stype=int[]
);

postgres=# select array_stack(array[val]) from x;
array_stack
---
 {{1,2,3},{3,4,5}}
(1 row)

merlin

-- 
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] A questions on planner choices

[Scott Marlowe]

On Fri, Aug 19, 2011 at 2:37 PM, Edoardo Panfili  wrote:
>
>  work_mem = 1MB
>  random_page_cost = 4
>
> I am using an SSD but the production system uses a standard hard disk.
>
> I did a try also with
> set default_statistics_target=1;
> vacuum analyze cartellino;
> vacuum analyze specie; -- the base table for specienomi
> vacuum analyze confini_regioni;
>
> but is always 4617.023 ms

OK, try turning up work_mem for just this connection, i.e.:

psql mydb
set work_mem='64MB';
explain analyze select  ;

and see if you get a different plan.  Often you only need a slightly
higher work_mem to get a better plan.  We're looking for a hash_join
to occur here, which should be much much faster.  After testing you
can set work_mem globally in the postgresql.conf file.  Try to keep it
smallish, as it's per sort per connection, so usage can go up really
fast with a lot of active connections and swamp your server's memory.
I run a 128G memory machine with ~500 connections and have it set to
16MB.

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

[pasman pasmański]

Array_agg is not implemented for arrays.

-- 

pasman

-- 
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] A questions on planner choices

[Tom Lane]

Edoardo Panfili  writes:
> [ poor plan for a Postgis query with ]
> Postgres 8.4.8 with postgis 1.5.3

I think that most of the issue here is poor selectivity estimation for
the Postgis operations, particularly &&.  I suggest that you should ask
about this on the postgis mailing lists.  They might well tell you to
try a newer release --- they may have improved things since 1.5.3.

> NOTICE:  LWGEOM_gist_joinsel called with incorrect join type

You should *definitely* report that to the Postgis guys, because it's a
bug.

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] A questions on planner choices

[Edoardo Panfili]

Il 20/08/11 04:28, Tom Lane ha scritto:

Edoardo Panfili  writes:

[ poor plan for a Postgis query with ]
Postgres 8.4.8 with postgis 1.5.3


I think that most of the issue here is poor selectivity estimation for
the Postgis operations, particularly&&.  I suggest that you should ask
about this on the postgis mailing lists.  They might well tell you to
try a newer release --- they may have improved things since 1.5.3.

1.5.3 is the latest stable release, I am downloading 2.0.0SVN



NOTICE:  LWGEOM_gist_joinsel called with incorrect join type


You should *definitely* report that to the Postgis guys, because it's a
bug.

I will do it

Thank you
Edoardo Panfili

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