[GENERAL] [Fwd: failure notice]

2010-05-17 Thread John R Pierce 
hmmm.  someone on this list has an overactive spam filter.  leaving full 
headers and stuff so the list admins can decide what to do about it, if 
anything.


btw, if the user at the bouncing domain happens to see this its very 
VERY rude to bounce spam based on the 'from' address as 90% of -real- 
spam has forged from addresses, so you'll just be spamming some innocent 
third party's mailbox.




 Original Message 
Subject:failure notice
Date:   18 May 2010 02:45:44 -
From:   mailer-dae...@burntmail.com
To: pie...@hogranch.com



Hi. This is the qmail-send program at burntmail.com.
I'm afraid I wasn't able to deliver your message to the following addresses.
This is a permanent error; I've given up. Sorry it didn't work out.

:
Sorry - The message you have sent was identified as spam by Spam Assassin 
(message bounced)

--- Below this line is a copy of the message.

Return-Path: 
Received: (qmail 17830 invoked from network); 18 May 2010 02:45:43 -
Received: from unknown (74.52.149.146)
 by burntmail.com with SMTP; 18 May 2010 02:45:43 -

From pgsql-general-owner+m163...@postgresql.org Mon May 17 21:43:53 2010

Return-path: 
Envelope-to: guyr-...@burntmail.com
Delivery-date: Mon, 17 May 2010 21:43:53 -0500
Received: from [200.46.204.254] (helo=mx2.hub.org)
by mx01.burntspam.com with esmtp (Exim 4.63)
(envelope-from )
id 1OECmf-0004PR-Ge
for guyr-...@burntmail.com; Mon, 17 May 2010 21:43:53 -0500
Received: from postgresql.org (mail.postgresql.org [200.46.204.86])
by mx2.hub.org (Postfix) with ESMTP id A66983EACB2F;
Mon, 17 May 2010 20:39:51 -0300 (ADT)
Received: from maia.hub.org (maia-2.hub.org [200.46.204.251])
by mail.postgresql.org (Postfix) with ESMTP id BEC40633798
for ; Mon, 17 May 
2010 20:39:19 -0300 (ADT)
Received: from mail.postgresql.org ([200.46.204.86])
by maia.hub.org (mx1.hub.org [200.46.204.251]) (amavisd-maia, port 10024)
with ESMTP id 98420-01
for ;
Mon, 17 May 2010 23:39:11 + (UTC)
X-BMDA-Edge: IPR=1,DYN=0,SRB=0,SPM=8.5,BTS=0,RBL=0,HIS=0,WHT=0,STR=0
X-Greylist: from auto-whitelisted by SQLgrey-1.7.6
Received: from hogranch.com (hogranch.com [75.101.82.47])
by mail.postgresql.org (Postfix) with ESMTP id 85F456336DF
for ; Mon, 17 May 2010 20:39:12 -0300 
(ADT)
Received: from [192.168.0.2] (porker [192.168.0.2])
by hogranch.com (8.11.6/8.11.6) with ESMTP id o4HNd2L26781
for ; Mon, 17 May 2010 16:39:02 -0700
Message-ID: <4bf1d391.7000...@hogranch.com>
Date: Mon, 17 May 2010 16:38:57 -0700
From: John R Pierce 
User-Agent: Thunderbird 2.0.0.24 (Windows/20100228)
MIME-Version: 1.0
To: pgsql-general@postgresql.org
Subject: Re: [GENERAL] which function should i invoke to create a table and
insert tuples?
References:  
<4bf1c0ea.3080...@burntmail.com> 

In-Reply-To: 
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit
X-HawgScanner-Information: Please contact the ISP for more information
X-HawgScanner: Found to be clean
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=-1.91 tagged_above=-5 required=5 tests=BAYES_00=-1.9,
T_RP_MATCHES_RCVD=-0.01
X-Spam-Level: 
X-Mailing-List: pgsql-general

List-Archive: 
List-Help: 
List-ID: 
List-Owner: 
List-Post: 
List-Subscribe: 
List-Unsubscribe: 
Precedence: bulk
Sender: pgsql-general-ow...@postgresql.org
X-Relayed-For: (mx2.hub.org) [200.46.204.254]

sunpeng wrote:
it's in source codes,actually i'm writting codes in postgresql source 
codes,just to verify some of my ideas. C language is used.


you would pass the SQL statements to do what you want to the various 
libpq library functions...



something like...

   PGconn *conn;
   PGresult *res;

   conn = PQconnectdb("dbname=mydatabase");
   if (PQstatus(conn) != CONNECTION_OK) {
   fprintf(stderr, "Connection to database failed: %s",
   PQerrorMessage(conn));
   exit_nicely(conn);
   }

   res = PQexec(conn, "create table test (id serial, num int, value
   text);");
   if (PQresultStatus(res) != PGRES_COMMAND_OK) {
   fprintf(stderr, "BEGIN command failed: %s", PQerrorMessage(conn));
   PQclear(res);
   exit_nicely(conn);
   }
   ...



most folks would probably put the PQexec() and status tests into a 
function to simplify things.



--
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] which function should i invoke to create a table and insert tuples?

2010-05-17 Thread John R Pierce 

sunpeng wrote:
it's in source codes,actually i'm writting codes in postgresql source 
codes,just to verify some of my ideas. C language is used.


you would pass the SQL statements to do what you want to the various 
libpq library functions...



something like...

   PGconn *conn;
   PGresult *res;

   conn = PQconnectdb("dbname=mydatabase");
   if (PQstatus(conn) != CONNECTION_OK) {
   fprintf(stderr, "Connection to database failed: %s",
   PQerrorMessage(conn));
   exit_nicely(conn);
   }

   res = PQexec(conn, "create table test (id serial, num int, value
   text);");
   if (PQresultStatus(res) != PGRES_COMMAND_OK) {
   fprintf(stderr, "BEGIN command failed: %s", PQerrorMessage(conn));
   PQclear(res);
   exit_nicely(conn);
   }
   ...



most folks would probably put the PQexec() and status tests into a 
function to simplify things.



--
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] which function should i invoke to create a table and insert tuples?

2010-05-17 Thread sunpeng 
it's in source codes,actually i'm writting codes in postgresql source
codes,just to verify some of my ideas. C language is used.

2010/5/17 Guy Rouillier 

> On 5/17/2010 10:31 AM, sunpeng wrote:
>
>> hi,when i do experiment on postgresql 8.4,i need to create a table and
>> insert some tuples,which function should i invoke?
>> for example,i want to create a table with "create table test (uid
>> int,catcode int)" and insert tuples with "insert into test values(1,1)".
>> thanks millions!
>>
>
> What do you mean by function?  Are you trying to do this from PgAdmin, from
> a stored proc or from some flavor of source code?  If either of the latter
> two, which language?
>
> --
> Guy Rouillier
>
> --
> 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] which function should i invoke to create a table and insert tuples?

2010-05-17 Thread Guy Rouillier 

On 5/17/2010 10:31 AM, sunpeng wrote:

hi,when i do experiment on postgresql 8.4,i need to create a table and
insert some tuples,which function should i invoke?
for example,i want to create a table with "create table test (uid
int,catcode int)" and insert tuples with "insert into test values(1,1)".
thanks millions!


What do you mean by function?  Are you trying to do this from PgAdmin, 
from a stored proc or from some flavor of source code?  If either of the 
latter two, which language?


--
Guy Rouillier

--
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] Fwd: Google Alert - postgresql 8.4

2010-05-17 Thread Thom Brown 
On 17 May 2010 16:00, Richard Broersma  wrote:
>
> Is this announcement premature?  I don't see where to download 8.4.4.
>
> -- Forwarded message --
> From: Google Alerts 
> Date: Mon, May 17, 2010 at 5:18 AM
> Subject: Google Alert - postgresql 8.4
> To: richard.broer...@gmail.com
>
>
> Google News Alert for: postgresql 8.4
>
> PostgreSQL developers fix vulnerabilities
> The H
> PostgreSQL versions 8.0 to 8.4 and 7.4 are affected by the update. 
> Installation packages and source code are available online. 8.4.4, 8.3.11, 
> 8.2.17, ...

Erk... yeah, that does appear to be premature.  The site doesn't
reflect that announcement.

Thom

-- 
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] Why Performance of SQL Query is *much* Slower in GUI PgAdmin

2010-05-17 Thread Guillaume Lelarge 
Le 17/05/2010 07:52, Yan Cheng CHEOK a écrit :
> The pgadmin result seems different with my machine. My friend and I are using 
> Windows machine. Are you using Linux machine?
> 

My tests this morning were on a Linux machine. I tried tonight with a
Windows PC. Here are the results for three "create database":

pgAdmin: 7.6s ; 8.5s ; 7.6s
psql:6.8s ; 7.4s ; 7.4s

Not much of a difference either (between the GUI tool (pgAdmin) and the
CLI tool (psql)). But a really big one between Linux and Windows.

PG 8.4.4, pgAdmin 1.12b1+ (both on Linux and Windows).


-- 
Guillaume.
 http://www.postgresqlfr.org
 http://dalibo.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] pg_dumpall custom format?

2010-05-17 Thread Guillaume Lelarge 
Le 17/05/2010 14:28, Thom Brown a écrit :
> On 13 May 2010 18:28, Guillaume Lelarge  wrote:
> 
>> Le 13/05/2010 19:24, Thom Brown a écrit :
>>> On 13 May 2010 17:49, Guillaume Lelarge  wrote:
>>>
 Le 13/05/2010 14:12, Thom Brown a écrit :
> On 5 March 2009 12:08, Thom Brown  wrote:
>> [...]
> I'm bringing this thread back to life to see if there are any further
> thoughts on this.  It would be nice to have a complete backup of a
 database
> cluster in custom format, and use pg_restore to restore an individual
> database in the same way that it can restore individual schemas and
 tables
> etc.
>

 Nope. But this is something I'd like to have too. I didn't actually look
 into it much more (I mean, in the code), but I intend to work on this
 for 9.1 (and other pg_dump stuff).



>>> You're now my favourite PostgreSQL developer!  Thanks Guillaume!
>>>
>>
>> Don't get your expectations too high. I just said I'll try :)
>>
>>
> I guess this will also require changes to pg_restore to support:
> 
> -g globals only
> -r roles only
> -t tablespaces (conflicts with pg_restore's table specification of -t)
> 

Yes.

> And of course being able to restore individual databases and specifying
> restoration of a table/function/trigger within specific databases.  I guess
> that could be done by specifying a database to restore, then asking for a
> single function.
> 
> e.g.:
> 
> pg_restore --database=my_source_database -P my_function(argtype) -d
> my_destination_database /database/backup/file
> 

Yes.

> And adding database entry support to the listing file.
> 

And adding a role and a tablespace entries. And prefixing every object
with the database they belong too.

It won't be easy, but it's really something lacking right now. Or at
least, I think so :)


-- 
Guillaume.
 http://www.postgresqlfr.org
 http://dalibo.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] New PostgreSQL RPM sets are available for Fedora / RHEL / CentOS

2010-05-17 Thread Devrim GÜNDÜZ 

PostgreSQL New RPM Sets
2010-05-17

Versions: 8.4.4 8.3.11 8.2.17, 8.1.21, 8.0.25, 7.4.29
-

-
Release Info:

The PostgreSQL RPM Building Project has released RPMs for 8.4.4 8.3.11
8.2.17, 8.1.21, 8.0.25, 7.4.29, and they are available in PostgreSQL RPM
Repository:

http://yum.pgrpms.org

We currently have RPMs for:

- Fedora 7
- Fedora 7-x86_64
- Fedora 8
- Fedora 8-x86_64
- Fedora 12
- Fedora 12-x86_64
- Red Hat Enterprise Linux Enterprise Server 4 - i386
- Red Hat Enterprise Linux Enterprise Server 4 - x86_64
- Red Hat Enterprise Linux Advanced Server 4 - i386
- Red Hat Enterprise Linux Advanced Server 4-x86_64
- Red Hat Enterprise Linux 5 - i386
- Red Hat Enterprise Linux 5 x86_64
- CentOS 5 - i386
- CentOS 5 - x86_64

More will come later.

You can also follow our news from Twitter:

 http://twitter.com/pgrpms

Support for PostgreSQL 7.4 and 8.0 will be abandoned as of July, 2010,
after they are EOLed.

For complete list of changes in RPM sets, please refer to the changelogs
in the RPMs. Use
 rpm -q -changelog package_name
for querying the changelog.

Point releases generally do not require a dump/reload from the previous
point, but please see the Release Notes to confirm procedures for
upgrading, especially if your current version is older than the last
point release.

The SRPMs are also provided:

http://yum.pgrpms.org/srpms/

We also have howto documents about RPM installation of PostgreSQL:

http://yum.pgrpms.org/howtoyum.php 

Please follow the instructions before installing/upgrading.

If you experience problems with the RPMs or if you have feature
requests, please join

http://lists.pgrpms.org/mailman/listinfo/pgrpms-general


The project page is:

http://yum.pgrpms.org 

Please do not use these resources for issue running or using PostgreSQL
once it is installed.

Manual download (without using yum) is also possible:

http://yum.pgrpms.org/8.4
http://yum.pgrpms.org/8.3
http://yum.pgrpms.org/8.2
http://yum.pgrpms.org/8.1
http://yum.pgrpms.org/8.0
http://yum.pgrpms.org/7.4

Regards,
-- 
Devrim GÜNDÜZ
PostgreSQL Danışmanı/Consultant, Red Hat Certified Engineer
PostgreSQL RPM Repository: http://yum.pgrpms.org
Community: devrim~PostgreSQL.org, devrim.gunduz~linux.org.tr
http://www.gunduz.org  Twitter: http://twitter.com/devrimgunduz


signature.asc
Description: This is a digitally signed message part

Re: [GENERAL] postgres work_mem

2010-05-17 Thread Tom Lane 
akp geek  writes:
> I would like to know if there is a way to restrict
> individual users not to change the work_mem parameter for their session?

No.

> Reason I am asking is if there is 2GB RAM available, if one of the user want
> to set the work_mem to 2GB because query is running slow. it might cause a
> problem right?

If you're allowing people to issue SQL commands directly, they can
easily cause performance problems with or without touching work_mem.

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

[GENERAL] postgres work_mem

2010-05-17 Thread akp geek 
dear all -

I would like to know if there is a way to restrict
individual users not to change the work_mem parameter for their session?
Reason I am asking is if there is 2GB RAM available, if one of the user want
to set the work_mem to 2GB because query is running slow. it might cause a
problem right?


Regards

Re: [GENERAL] Fwd: Google Alert - postgresql 8.4

2010-05-17 Thread Thom Brown 
On 17 May 2010 17:08, Leif Biberg Kristensen  wrote:
> On Monday 17. May 2010 17.13.05 Thom Brown wrote:
>> >
>> > Is this announcement premature?  I don't see where to download 8.4.4.
>> >
>> > -- Forwarded message --
>> > From: Google Alerts 
>> > Date: Mon, May 17, 2010 at 5:18 AM
>> > Subject: Google Alert - postgresql 8.4
>> > To: richard.broer...@gmail.com
>> >
>> >
>> > Google News Alert for: postgresql 8.4
>> >
>> > PostgreSQL developers fix vulnerabilities
>> > The H
>> > PostgreSQL versions 8.0 to 8.4 and 7.4 are affected by the update.
> Installation packages and source code are available online. 8.4.4, 8.3.11,
> 8.2.17, ...
>>
>> Erk... yeah, that does appear to be premature.  The site doesn't
>> reflect that announcement.
>
> I'm on Gentoo Linux, and am compiling PostgreSQL 8.4.4 from portage (~x86) as
> I'm writing.
>
> regards,
> --

Yes, I noticed portage got it pretty quick!

Thom

-- 
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] Fwd: Google Alert - postgresql 8.4

2010-05-17 Thread Leif Biberg Kristensen 
On Monday 17. May 2010 17.13.05 Thom Brown wrote:
> >
> > Is this announcement premature?  I don't see where to download 8.4.4.
> >
> > -- Forwarded message --
> > From: Google Alerts 
> > Date: Mon, May 17, 2010 at 5:18 AM
> > Subject: Google Alert - postgresql 8.4
> > To: richard.broer...@gmail.com
> >
> >
> > Google News Alert for: postgresql 8.4
> >
> > PostgreSQL developers fix vulnerabilities
> > The H
> > PostgreSQL versions 8.0 to 8.4 and 7.4 are affected by the update. 
Installation packages and source code are available online. 8.4.4, 8.3.11, 
8.2.17, ...
> 
> Erk... yeah, that does appear to be premature.  The site doesn't
> reflect that announcement.

I'm on Gentoo Linux, and am compiling PostgreSQL 8.4.4 from portage (~x86) as 
I'm writing.

regards,
-- 
Leif Biberg Kristensen
http://solumslekt.org/blog/

-- 
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] Lightning Talks for PgCon! Submit yours today.

2010-05-17 Thread Selena Deckelmann 
Just a reminder -- We've got a few spaces left for lightning talks.
Submit your idea today!

See below for details.

-selena

On Sat, May 8, 2010 at 6:46 AM, Selena Deckelmann  wrote:
> Hi!
>
> We're having Lightning Talks again at PgCon - scheduled for 5:30pm on
> May 20th in Ottawa!
>
> Do you have a talk or idea you'd like to share? Lightning Talks are
> one of the most highly attended sessions because they are fast, fun,
> and useful (but not always).  Slides are not required. If you use
> them, you'll have to operate them as PDFs.
>
> Please send your 5-minute talk idea to . Slots fill
> up fast, so get them in now! We'll accept submissions until May 16 via
> email, and after that, you'll need to find me (Selena) at the
> conference if you'd like to be added.
>
> We can only accept 11 talks in the time allowed.  Selection is
> generally first-come, first-served.  I will not determine the order of
> the talks until the time of the session.
>
> More details are at:
>
> http://www.pgcon.org/2010/schedule/events/267.en.html
>
> --
> http://chesnok.com/daily - me
> http://endpoint.com - work
>



-- 
http://chesnok.com/daily - me

-- 
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] 'Infinity' in date columns?

2010-05-17 Thread Ken Winter 


> -Original Message-
> From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general-
> ow...@postgresql.org] On Behalf Of Tom Lane
...
> 
> "Ken Winter"  writes:
> > The documentation at
> > http://www.postgresql.org/docs/8.3/static/datatype-datetime.html seems
> to
> > say that the special value 'infinity' ("later than all other time
> stamps")
> > should work for an date-time column, and the type "date" is listed as
> among
> > the date-time data types.
> > But I can't get 'infinity' to work for columns of type "date".
> 
> You seem to be carefully reading around the middle column in Table 8-13,
> which specifically shows that infinity doesn't work for type date.

Oh, duh, right you are...
 
> Now, if you were to update to Postgres 8.4, it *would* work.

I'll see if I can get my host to do that.
 
~ Thanks
~ Ken




-- 
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] Why Performance of SQL Query is *much* Slower in GUI PgAdmin

2010-05-17 Thread Justin Graf 
On 5/17/2010 12:52 AM, Yan Cheng CHEOK wrote:
> The pgadmin result seems different with my machine. My friend and I are using 
> Windows machine. Are you using Linux machine?
>
> Thanks and Regards
> Yan Cheng CHEOK
>
>
> **snip**

I use both windows and Linux using pgadmin, and on occasion use psql


I take my work home so I backup then drop then recreate the databasse on 
my computers at home.  So I create the database at least 2 or 3 times a 
week.  I see no meaningful difference between machines or client used to 
create a DB using either Windows or Linux.   I have never timed it.  Why 
would anyone time creating a DB???  But i would say its less than second.

So something weird is going with your friends computer.


All legitimate Magwerks Corporation quotations are sent in a .PDF file 
attachment with a unique ID number generated by our proprietary quotation 
system. Quotations received via any other form of communication will not be 
honored.

CONFIDENTIALITY NOTICE: This e-mail, including attachments, may contain legally 
privileged, confidential or other information proprietary to Magwerks 
Corporation and is intended solely for the use of the individual to whom it 
addresses. If the reader of this e-mail is not the intended recipient or 
authorized agent, the reader is hereby notified that any unauthorized viewing, 
dissemination, distribution or copying of this e-mail is strictly prohibited. 
If you have received this e-mail in error, please notify the sender by replying 
to this message and destroy all occurrences of this e-mail immediately.
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

Re: [GENERAL] Fwd: Google Alert - postgresql 8.4

2010-05-17 Thread Thom Brown 
>
> Is this announcement premature?  I don't see where to download 8.4.4.
>
> -- Forwarded message --
> From: Google Alerts 
> Date: Mon, May 17, 2010 at 5:18 AM
> Subject: Google Alert - postgresql 8.4
> To: richard.broer...@gmail.com
>
>
> Google News Alert for: postgresql 8.4
>
> PostgreSQL developers fix vulnerabilities
> The H
> PostgreSQL versions 8.0 to 8.4 and 7.4 are affected by the update. 
> Installation packages and source code are available online. 8.4.4, 8.3.11, 
> 8.2.17, ...

Erk... yeah, that does appear to be premature.  The site doesn't
reflect that announcement.

Thom

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

[GENERAL] Fwd: Google Alert - postgresql 8.4

2010-05-17 Thread Richard Broersma 
Is this announcement premature?  I don't see where to download 8.4.4.

-- Forwarded message --
From: Google Alerts 
Date: Mon, May 17, 2010 at 5:18 AM
Subject: Google Alert - postgresql 8.4
To: richard.broer...@gmail.com


Google News Alert for: *postgresql 8.4*
 *PostgreSQL* developers fix
vulnerabilities
The H
*PostgreSQL* versions 8.0 to *8.4* and 7.4 are affected by the update.
Installation packages and source code are available online. *8.4*.4, 8.3.11,
8.2.17, *...*

--
 This as-it-happens Google Alert is brought to you by Google.

Removethis
alert.
Createanother
alert.
Manageyour
alerts.



-- 
Regards,
Richard Broersma Jr.

Visit the Los Angeles PostgreSQL Users Group (LAPUG)
http://pugs.postgresql.org/lapug

Re: [GENERAL] which function should i invoke to create a table and insert tuples?

2010-05-17 Thread Thom Brown 
On 17 May 2010 15:53, sunpeng  wrote:

> Thanks for your quickly reply.
> Maybe i haven't expressed clearly.My purpose is in the postgresql source
> codes which function invoke should i use to create table and insert tuples.
> for example are there any function just like _createTable(char
> *tableName,int firstColumn,int secondColumn) ?
>
>
Please use "reply to all" so everyone can see your responses.

I'm not aware of anything that will do what you're asking.  You can use
client interfaces (
http://www.postgresql.org/docs/8.4/static/client-interfaces.html) but as for
libraries with variadic functions, I wouldn't know.

Regards

Thom

Re: [GENERAL] which function should i invoke to create a table and insert tuples?

2010-05-17 Thread Thom Brown 
On 17 May 2010 15:31, sunpeng  wrote:

> hi,when i do experiment on postgresql 8.4,i need to create a table and
> insert some tuples,which function should i invoke?
> for example,i want to create a table with "create table test (uid
> int,catcode int)" and insert tuples with "insert into test values(1,1)".
> thanks millions!
>
> peng
>

What is it you're testing?

You can insert many rows into a table by doing something like the following:

CREATE TABLE test
(
test_id serial,
test_num int
test_value int
)

INSERT INTO test (test_num, test_value)
SELECT s.a, ceil(random()*100) FROM generate_series(1,1000) as s(a);

That would just put 1,000 entries into the table, the first column would get
its value from its sequence, the second from the series and the third would
be random.

Regards

Thom

[GENERAL] which function should i invoke to create a table and insert tuples?

2010-05-17 Thread sunpeng 
hi,when i do experiment on postgresql 8.4,i need to create a table and
insert some tuples,which function should i invoke?
for example,i want to create a table with "create table test (uid
int,catcode int)" and insert tuples with "insert into test values(1,1)".
thanks millions!

peng

Re: [GENERAL] Getting reference key elements in right orders

2010-05-17 Thread Tom Lane 
seil...@so-net.net.tw writes:
> I need to know the match columns of referencing and referenced keys. 

The query you show definitely won't match them up correctly, since that
=ANY test is not order-sensitive.  What you need to do is generate the
integers from 1 to array_length(conkey) and then join on attnum =
conkey[i], rather than using =ANY.  You can find some examples in the
information_schema views.  (In fact, you might well find that the
information_schema views are close enough already to what you need.)

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] PANIC: corrupted item pointer: 32766

2010-05-17 Thread Catalin BOIE 
On 05/14/2010 08:49 PM, Igor Neyman wrote:
>  
> 
>> -Original Message-
>> From: Catalin BOIE [mailto:cboie-pg...@66.com] 
>> Sent: Friday, May 14, 2010 5:43 AM
>> To: pgsql-general@postgresql.org
>> Subject: Re: PANIC: corrupted item pointer: 32766
>>
>> Some more info.
>>
>> The PANIC happens several times per minute, so, is really bad for me.
>>
>> I tried to narrow down based on a field (timestamp) and I 
>> found some bad "points", but I cannot delete them (same PANIC 
>> message appear).
>>
>> Do you have any idea how can I correct that entries?
>>
>> The worry part is how this happened?!
>>
>> Thank you!
>>
>> RAM is 16GiB, 16 "cpus" (including hyperthreading).
>>
>> On 05/14/2010 09:32 AM, Catalin BOIE wrote:
>>> Hello!
>>>
>>> I have a serious problem with one of my tables.
>>>
>>> Version: postgresql-server-8.4.3-1.fc12.x86_64
>>> Kernel: kernel-2.6.32.11-99.fc12.x86_64
>>>
>>> I reindexed all indexes on that table, but I still cannot 
>> workaround 
>>> this problem.
>>>
>>> Memory is ECC and the storage is RAID10 (BIOS reported it OK).
>>>
>>> How I can fix this problem?
>>>
>>> Thank you!
>>>
>>
>>
>> --
>> Catalin BOIE
>>
> 
> If you can read other (good) records from this table, then:
> 
> 1. create "intermediate table
> 2. copy all "good" records from original table into the new table
> 3. drop original table
> 4. rename "intermediate" table to "original" name
> 5. re-created required indexes (and any other objects dependent on this
> table)
> 
> 
> Igor Neyman
> 

Only now I seen your message, but this is exactly what I did.

I built a script that did this:

for (uid = 1; uid < 'SELECT MAX(uid) FROM table'; uid++)
INSERT INTO new_table SELECT * FROM table WHERE uid = $uid

uid is the primary key and is a sequence.

I managed to recover all rows (around 5 millions), except 22, that where
inserted at the same time.

Seems that not the rows where bad, but the link to a page where they
were stored. I am not familiar with the PostgreSQL code. I am also not
familiar with the structure on mem/disk of the database. So, have mercy
with my explanations.
I am very sure that rows were inserted one after another.

Having no more ideas, I dropped all the indexes to the bad table. No change.

ANALYZE worked.
VACUUM generated the same error message.

fsync was at the default value (I assume is on).

For now I fixed the problem, but I am worried that the problem will come
back, because I have no idea what could generate this corruption.

Maybe, would be nice that this message to dump more data to help
somebody to debug the problem.
I searched this error and I seen that there are only around 5 cases. So,
is seldom, but is a bad one.


Thanks to everybody for their support!

P.S. I still have the bad table. If you want me to debug something, just
ask.

-- 
Catalin BOIE

-- 
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] PANIC: corrupted item pointer: 32766

2010-05-17 Thread Catalin BOIE 
On 05/14/2010 07:16 PM, Emanuel Calvo Franco wrote:
>> The PANIC happens several times per minute, so, is really bad for me.
>>
>> I tried to narrow down based on a field (timestamp) and I found some bad
>> "points", but I cannot delete them (same PANIC message appear).
>>
>> Do you have any idea how can I correct that entries?
>>
>> The worry part is how this happened?!
>>
> 
> Do you have fsync turned off?

I left it at the default value, so I assume it was on.

-- 
Catalin BOIE

-- 
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] 'Infinity' in date columns?

2010-05-17 Thread Lew 

Ken Winter wrote:

The documentation at
http://www.postgresql.org/docs/8.3/static/datatype-datetime.html
seems to say that the special value ‘infinity’ (“later than all
other time stamps”) should work for an date-time column, and the
type “date” is listed as among the date-time data types.

But I can’t get ‘infinity’ to work for columns of type “date”.


Shoaib Mir wrote:

I don't have version 8.3 with me right now but I just gave it a try with
8.4 and it gave me the expected output:


As Tom Lane points out, that's a difference between 8.3 and 8.4.

--
Lew

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

Re: [GENERAL] pg_dumpall custom format?

2010-05-17 Thread Thom Brown 
On 13 May 2010 18:28, Guillaume Lelarge  wrote:

> Le 13/05/2010 19:24, Thom Brown a écrit :
> > On 13 May 2010 17:49, Guillaume Lelarge  wrote:
> >
> >> Le 13/05/2010 14:12, Thom Brown a écrit :
> >>> On 5 March 2009 12:08, Thom Brown  wrote:
>  [...]
> >>> I'm bringing this thread back to life to see if there are any further
> >>> thoughts on this.  It would be nice to have a complete backup of a
> >> database
> >>> cluster in custom format, and use pg_restore to restore an individual
> >>> database in the same way that it can restore individual schemas and
> >> tables
> >>> etc.
> >>>
> >>
> >> Nope. But this is something I'd like to have too. I didn't actually look
> >> into it much more (I mean, in the code), but I intend to work on this
> >> for 9.1 (and other pg_dump stuff).
> >>
> >>
> >>
> > You're now my favourite PostgreSQL developer!  Thanks Guillaume!
> >
>
> Don't get your expectations too high. I just said I'll try :)
>
>
I guess this will also require changes to pg_restore to support:

-g globals only
-r roles only
-t tablespaces (conflicts with pg_restore's table specification of -t)

And of course being able to restore individual databases and specifying
restoration of a table/function/trigger within specific databases.  I guess
that could be done by specifying a database to restore, then asking for a
single function.

e.g.:

pg_restore --database=my_source_database -P my_function(argtype) -d
my_destination_database /database/backup/file

And adding database entry support to the listing file.

Thom

[GENERAL] Getting reference key elements in right orders

2010-05-17 Thread seiliki 
Hi!

I need to know the match columns of referencing and referenced keys. 

CREATE TABLE referenced (c1 TEXT,c2 TEXT, PRIMARY KEY(c1,c2));

CREATE TABLE referencing (c1 TEXT,c2 TEXT,c3 TEXT, PRIMARY KEY(c1,c2,c3), 
CONSTRAINT fk FOREIGN KEY (c1,c2) REFERENCES referenced (c1,c2));

The following SQL is similar to pg_get_constraintdef():

SELECT
  ARRAY_TO_STRING(ARRAY(SELECT attname FROM pg_attribute WHERE 
attrelid=master.oid AND attnum=ANY(confkey)),';') AS master_columns
  ,ARRAY_TO_STRING(ARRAY(SELECT attname FROM pg_attribute WHERE 
attrelid=detail.oid AND attnum=ANY(conkey)),';') AS detail_columns
FROM pg_class master,pg_class detail,pg_constraint
WHERE master.relname='referenced' AND detail.relname='referencing'
  AND confrelid=master.oid
  AND conrelid=detail.oid
  AND contype='f' AND confupdtype='c' AND confdeltype='c'

It appears to do the job like this:

master_columns  detail_columns
--
c1;c2   c1;c2

However, I am not sure the referencing and referenced key elements in the above 
selected strings, master_columns and detail_columns, are guaranteed to be in 
correct order. I suspect they will become these from time to time:

master_columns  detail_columns
--
c1;c2   c2;c1

I am thinking that sorting subscripts of array "pg_constraint.confkey" should 
guarantee the correct order, but I have no idea how to do that.

My questions are:

Is the above SQL reliable?
If it is not, how to make it reliable?

Thank you in advance!

CN

-- 
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] Why Performance of SQL Query is *much* Slower in GUI PgAdmin

2010-05-17 Thread Scott Marlowe 
On Mon, May 17, 2010 at 12:02 AM, Scott Marlowe  wrote:
> On Sun, May 16, 2010 at 7:21 PM, Yan Cheng CHEOK  wrote:
>> Recently, I try to introduce my friend to use PostgreSQL.
>>
>> However, he first impression is that. PostgreSQL is much slower compared to 
>> MySQL. He realize he has to wait for 7 seconds, to create a tmp database.
>>
>> He run a command through pgadmin GUI.
>>
>> "CREATE DATABASE TMP;"
>>
>> I try to run through command prompt instead. It tooks 2 seconds.
>
> Maybe he's running an older version that hasn't had create db
> optimized?

Just to point out, there was a discussion I can't find right now about
fsync and create database optimization earlier this year.  Apparently
grouping everything together resulted in a big performance gain for
create database.  So it really is likely that a different version
could be much slower.  However, if you're both hitting the same db,
then the slowness is in the gui tool, not pgsql.

-- 
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] Why Performance of SQL Query is *much* Slower in GUI PgAdmin

2010-05-17 Thread Thomas Kellerer 

Scott Marlowe, 17.05.2010 10:58:

Why on earth would anybody compare database performance using a command that
is usually executed only once in the lifetime of a database?

It's like saying "The car from manufacturer A is slower than the one from
manufacturer B, because it takes 1 second longer to start the engine..."


I refer you to this classic post on the subject:
http://www.mail-archive.com/pgsql-general@postgresql.org/msg93043.html



Nice one :)

That reminds me of a "fun" car test in a German car magazine some years ago.

They were comparing the cheapest and the most expensive car from the Fiat group:
a Fiat 127 (similar to a Fiat 500 nowadays) against a Ferarri Testarossa.

The first test was to get 3 people from Munich to Augsburg (approx. 70km), the 
Ferarri lost with about 30 minutes difference because it had to drive two times 
;)

Regards
Thomas



--
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] Why Performance of SQL Query is *much* Slower in GUI PgAdmin

2010-05-17 Thread Scott Marlowe 
On Mon, May 17, 2010 at 2:35 AM, Thomas Kellerer  wrote:
> Yan Cheng CHEOK, 17.05.2010 03:21:
>>
>> Recently, I try to introduce my friend to use PostgreSQL.
>>
>> However, he first impression is that. PostgreSQL is much slower
>> compared to MySQL. He realize he has to wait for 7 seconds, to create
>> a tmp database.
>>
>
> Why on earth would anybody compare database performance using a command that
> is usually executed only once in the lifetime of a database?
>
> It's like saying "The car from manufacturer A is slower than the one from
> manufacturer B, because it takes 1 second longer to start the engine..."

I refer you to this classic post on the subject:
http://www.mail-archive.com/pgsql-general@postgresql.org/msg93043.html

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

Re: [GENERAL] Why Performance of SQL Query is *much* Slower in GUI PgAdmin

2010-05-17 Thread Thomas Kellerer 

Yan Cheng CHEOK, 17.05.2010 03:21:

Recently, I try to introduce my friend to use PostgreSQL.

However, he first impression is that. PostgreSQL is much slower
compared to MySQL. He realize he has to wait for 7 seconds, to create
a tmp database.



Why on earth would anybody compare database performance using a command that is 
usually executed only once in the lifetime of a database?

It's like saying "The car from manufacturer A is slower than the one from 
manufacturer B, because it takes 1 second longer to start the engine..."

Thomas


--
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] Why Performance of SQL Query is *much* Slower in GUI PgAdmin

2010-05-17 Thread Scott Marlowe 
Here's a more useful test.

write a simple script that creates a 100 or so dbs.  Run it against
each database, and while it's running, pull the power plug.  Plug back
in, see if your database comes back up uncorrupted.

If pgsql is on proper db hardware (i.e. it obeys fsync, has battery
backed cache on a RAID controller, etc) it should come up no problems.
 Since the system catalogs are stored in the same transactional engine
/ table type as everything else.  On MySQL, maybe, maybe not, as the
system catalogs are always stored in MyISAM tables even if you dont'
use them for anything else.

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