Lim Berger [EMAIL PROTECTED] writes:
Here's the table definition:
Table public.cachedstats
Column | Type | Modifiers
---+---+--
id|
How on earth can i turn off PgAdmin grabbing default program binding
for .sql files on OS X?
Kristo Kaiv
http://kaiv.wordpress.com (PostgreSQL blog)
I'm seeing an obstacle in my aim to migrate from mysql to PG mainly from
the manner in which PG handles duplicate entries either from primary
keys or unique entries.
Data is taken from perl DBI into (right now) CSV based files to be used
via psql's \copy command to insert into the table.
In
Hi all,
Can anyone please tell me how to download the PostgreSQL-7.1 source code
through CVS, i use the following set of commands to get the source code
of postgreSQL.
1) cvs -d :pserver:[EMAIL PROTECTED]:/projects/cvsroot
login
This command will do an initial login to the PostgreSQL
On Aug 13, 2007, at 11:54 PM, Lim Berger wrote:
I am testing through PHP microtime function. The query is administered
through pg_query() function of PHP. I know there could be some latency
coming in from the PHP's PG functions' overhead, but not such an order
of magnitude different from
On Mon, 13 Aug 2007 11:30:37 -0500
Scott Marlowe [EMAIL PROTECTED] wrote:
Oh, one other thing that contributes to the problem
Thanks for the replies - all of this was very useful info.
Josh
---(end of broadcast)---
TIP 2: Don't 'kill -9'
On 8/14/07, Sander Steffann [EMAIL PROTECTED] wrote:
Hi Lim,
It might also be in /etc/security/limits.conf.
Thanks. I see these two lines in that file:
postgressoftnofile 8192
postgreshardnofile 8192
How should I change these values? I am not sure how
Hi,
Now if I want a maintenance_work_mem of 64M for Postgresql, what
should the max user processes setting be in my ulimit, or the open
files setting etc? Is there a Postgresql help or doc page I can read
to see how these values map? I'd like to be more educated in how I
test to tweak these
Kristo Kaiv wrote:
How on earth can i turn off PgAdmin grabbing default program binding
for .sql files on OS X?
Select a .sql file, right-click (ctrl-click) and choose Get Info (or
just select the file and hit CMD-I). Expand the Open With ... tab,
choose Other and select the app you want
I have been a long time user of mysql. Switching to Postgresql because
the true features included in 5.1 (as of this moment) are nothing to
write home about. The InnoDB stuff is highly advocated but it has its
own set of issues, and when one looks at things like backup/restore
etc, it is clearly
Kuriakose, Cinu Cheriyamoozhiyil [EMAIL PROTECTED] writes:
2) cvs -z3 -d :pserver:[EMAIL PROTECTED]:/projects/cvsroot
co -P pgsql
This will fetch the code, but the code that is fetched is the latest
code.
You need -r RELx_y_STABLE to check out the branch for release x.y.
Can anyone please
Phoenix Kiula [EMAIL PROTECTED] writes:
Though other threads I have learned that multiple inserts or updates
can be sped up with:
[QUOTE]
- BEGIN TRANSACTION;
- INSERT OR UPDATE queries, ideally PREPAREd;
- COMMIT;
[/QUOTE]
QUESTION1:
Is this it? Or am I missing something in terms of
We recently moved to PITR backup and recovery solution as defined in the
documentation. Our basic setup executes the backup start command, and then
takes a snapshot of the filesystem and backups wal files. However, we have
database files ( not wal files ) that change while the system is in
In response to [EMAIL PROTECTED] [EMAIL PROTECTED]:
We recently moved to PITR backup and recovery solution as defined in the
documentation. Our basic setup executes the backup start command, and then
takes a snapshot of the filesystem and backups wal files. However, we have
database files (
On 14/08/07, Gregory Stark [EMAIL PROTECTED] wrote:
Phoenix Kiula [EMAIL PROTECTED] writes:
Though other threads I have learned that multiple inserts or updates
can be sped up with:
[QUOTE]
- BEGIN TRANSACTION;
- INSERT OR UPDATE queries, ideally PREPAREd;
- COMMIT;
[/QUOTE]
On 8/14/07, Ow Mun Heng [EMAIL PROTECTED] wrote:
I'm seeing an obstacle in my aim to migrate from mysql to PG mainly from
the manner in which PG handles duplicate entries either from primary
keys or unique entries.
Data is taken from perl DBI into (right now) CSV based files to be used
via
On Tue, Aug 14, 2007 at 11:09:36PM +0800, Phoenix Kiula wrote:
If I am reading this right, does this mean it is probably better to
leave fsync as fsync=off on production machines?
No, you want fsync=on on any machine which holds data you
care about. And you want hardware which doesn't lie to
On Tue, 2007-08-14 at 10:52 -0400, [EMAIL PROTECTED] wrote:
We recently moved to PITR backup and recovery solution as defined in
the documentation. Our basic setup executes the backup start command,
and then takes a snapshot of the filesystem and backups wal files.
However, we have database
Thank you AM. Very useful note, must appreciate the info you shared.
About COPY, I have two simple questions:
1. Is there anything like an ALTER DATABASE command? I would like to
change the character set without having to recreate the DATABASE
again!
2. Also, when I do a mysqldump I seem to be
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
On 08/14/07 10:09, Phoenix Kiula wrote:
On 14/08/07, Gregory Stark [EMAIL PROTECTED] wrote:
Phoenix Kiula [EMAIL PROTECTED] writes:
[snip]
QUESTION1:
Is this it? Or am I missing something in terms of execution? We use
Perl on our site but may
Phoenix Kiula [EMAIL PROTECTED] writes:
If I am reading this right, does this mean it is probably better to
leave fsync as fsync=off on production machines?
Only if you can reconstruct your data from other sources in the case of a
server crash or power failure. I wouldn't recommend it.
Phoenix Kiula wrote:
Agree. That's what I am trying to do. Include as many UPDATEs etc into
the same TRANSACTION block, but my worry is when I read about
autocommit and how it is enabled by default in postgresql 8.
Specifying explicit BEGIN and COMMIT blocks should only commit when I
want the
On 8/14/07, Alban Hertroys [EMAIL PROTECTED] wrote:
*And* you can define compound foreign key constraints,
for example:
CREATE TABLE employee (
employee_id serial NOT NULL,
company_id integer NOT NULL,
name text NOT NULL,
CONSTRAINT employee_pk
I've been working on a db project intended to perform modifications to one db
based on the data stored in another one. Specifically, I'm supposed to check a
pair of tables based on two criteria; an id field, and a timestamp. This will
be crossreferenced with the second database; the
You're confusing CHECK constraints and FOREIGN KEY constraints. They're
different things ;)
CHECK constraints verify that data in a certain column matches a certain
condition. I'm not sure they can reference columns in other tables,
unless you wrap those checks in stored procedures maybe...
Andrew Edson wrote:
The following is a copy of my index creation statement, index name, and
explain and explain analyze output on the statement I was trying to run. Would
someone please help me figure out what I'm doing wrong here?
attest=# create index ptrans_cid_trandt_idx on
If you issue a BEGIN then nothing gets committed until you issue a COMMIT. If
anything happens in the meantime then everything you've done since the BEGIN
disappears.
There are some cases where I would like to bunch queries into a
transaction purely for speed purposes, but they're not
In response to Phoenix Kiula [EMAIL PROTECTED]:
If you issue a BEGIN then nothing gets committed until you issue a COMMIT.
If
anything happens in the meantime then everything you've done since the BEGIN
disappears.
There are some cases where I would like to bunch queries into a
Hi,
is there a way to have PostgreSQL insert a new row into a table during
a SELECT query if no row is found by that query?
--
Best regards,
Michal mailto:[EMAIL PROTECTED]
---(end of broadcast)---
TIP 3: Have you
I want to know whether MVCC has cons also. Is it heavy on resources? How
PGSQL MVCC relates with SQL Server 2005 new Snapshot Isolation.
--
View this message in context:
http://www.nabble.com/MVCC-cons-tf4268841.html#a12149505
Sent from the PostgreSQL - general mailing list archive at
Phoenix Kiula wrote on 14.08.2007 19:46:
There are some cases where I would like to bunch queries into a
transaction purely for speed purposes, but they're not interdependent
for integrity. E.g.,
BEGIN TRANSACTION;
UPDATE1;
UPDATE2;
UPDATE3;
COMMIT;
If UPDATE2 fails because it, say,
On 8/14/07, Bill Moran [EMAIL PROTECTED] wrote:
But the rule is, if any query within the transaction errors, then all
queries
within the transaction are rolled back.
This is the default behaviour, but with psql and ON_ERROR_ROLLBACK parameter
the behaviour can be changed. See
RPK wrote:
I want to know whether MVCC has cons also. Is it heavy on resources? How
PGSQL MVCC relates with SQL Server 2005 new Snapshot Isolation.
Speaking as an end-user, I can give only one I've ever seen, which is
performance. Because of MVCC, Postgres's write performance (insert and
You could do this with savepoints which are a kind of sub-transaction inside a
bigger transaction.
e.g.:
BEGIN TRANSACTION;
SAVEPOINT sp1;
UPDATE1;
IF (failed) rollback to savepoint sp1;
SAVEPOINT sp1;
UPDATE2;
IF (failed) rollback to savepoint sp2;
Thanks Thomas, this is a great
when i intentioally try to connect asynchronously to a database that does
not exist, i get
server closed the connection unexpectedly
My intention is to create the database if it does not exist ... Is there any
way retrive the actual error so i can know when to create the database?
thanx:)
On 8/14/07, RPK [EMAIL PROTECTED] wrote:
I want to know whether MVCC has cons also. Is it heavy on resources? How
PGSQL MVCC relates with SQL Server 2005 new Snapshot Isolation.
Of course it does. There ain't no such thing as a free lunch, after all.
PostgreSQL's mvcc implementation means
Kenneth Downs [EMAIL PROTECTED] writes:
Speaking as an end-user, I can give only one I've ever seen, which is
performance. Because of MVCC, Postgres's write performance (insert and
update) appears on my systems to be almost exactly linear to row size.
Inserting 1000 rows into a table with
I have a table with ten columns. My queries basically one column as
the first WHERE condition, so an index on that column is certain. But
the columns after that one vary depending on end-user's choice (this
is a reporting application) and so does the sorting order.
In MySQL world, I had
Tom Lane wrote:
Kenneth Downs [EMAIL PROTECTED] writes:
Speaking as an end-user, I can give only one I've ever seen, which is
performance. Because of MVCC, Postgres's write performance (insert and
update) appears on my systems to be almost exactly linear to row size.
Inserting 1000 rows
madhtr [EMAIL PROTECTED] writes:
when i intentioally try to connect asynchronously to a database that does
not exist, i get
server closed the connection unexpectedly
There's something wrong with your code then.
regards, tom lane
---(end of
Phoenix Kiula [EMAIL PROTECTED] writes:
There are some cases where I would like to bunch queries into a
transaction purely for speed purposes, but they're not interdependent
for integrity. E.g.,
How do you know you need to do this for speed if you haven't run it yet? I
would suggest you build
Don't forget to CC: the list.
Andrew Edson wrote:
I apologize; You are correct in that I mistyped my original structure. Here is
the information for the correct explain and explain analyze statements.
attest=# explain select cntrct_id, tran_dt from ptrans where rcrd_cd = '0A';
No need
ok thanx:)
... here's the source ... can u tell me whats wrong? (the purpose of this
function is to allow for thread safety on the connection, and allow for
cancellation if the connection takes too long)
BTW ...
- coninfo is host=localhost port=5432 dbname=testdb user=localuser
I need some help with rewriting a query. I have a fairly complicated
query (for me anyway) that dumps daily climate data, filling in
missing data with monthly averages (one line per day).
I want to output monthly averages (one line per month). I am having a
hard time wrapping my head
On 14.08.2007, at 17:13, brian wrote:
Select a .sql file, right-click (ctrl-click) and choose Get
Info (or just select the file and hit CMD-I). Expand the Open
With ... tab, choose Other and select the app you want to handle
these from your Applications directory.
brian
i am not such a
madhtr [EMAIL PROTECTED] writes:
... here's the source ... can u tell me whats wrong?
Well, your usage of pge seems fairly broken, in particular the random
(and wrong) assumptions about which values are or are not zero. AFAICT
this code doesn't really distinguish between PGRES_POLLING_FAILED
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
On 08/14/07 14:34, Kenneth Downs wrote:
Tom Lane wrote:
Kenneth Downs [EMAIL PROTECTED] writes:
Speaking as an end-user, I can give only one I've ever seen, which is
performance. Because of MVCC, Postgres's write performance (insert
and
[EMAIL PROTECTED] (Phoenix Kiula) writes:
I have a table with ten columns. My queries basically one column as
the first WHERE condition, so an index on that column is certain. But
the columns after that one vary depending on end-user's choice (this
is a reporting application) and so does the
Try to read about CREATE RULE in the documentation.
On 8/14/07, Michal Paluchowski [EMAIL PROTECTED] wrote:
Hi,
is there a way to have PostgreSQL insert a new row into a table during
a SELECT query if no row is found by that query?
--
Best regards,
Michal
Pconnects are absolutely necessary if we use tsearch2, because it
initializes its dictionaries on a first query in a session. It's a very
heavy process (500 ms and more). So, if we do not use pconnect, we waste
about 500 ms on each DB connection. Too much pain.
Or, of course, pconnect may be
On 08/14/07 14:34, Kenneth Downs wrote:
Tom Lane wrote:
Kenneth Downs [EMAIL PROTECTED] writes:
Speaking as an end-user, I can give only one I've ever seen, which is
performance. Because of MVCC, Postgres's write performance (insert
and update) appears on my systems to be almost
Kenneth Downs [EMAIL PROTECTED] writes:
RPK wrote:
I want to know whether MVCC has cons also. Is it heavy on resources? How
PGSQL MVCC relates with SQL Server 2005 new Snapshot Isolation.
Well the fundamental con of MVCC versus serializing everything using locks is
that you have to keep more
I did make an error on the zero assumption, ty :)
However, the reason PGRES_POLLING_FAILED and PGRES_POLLING_OK both break the
loop is because of this:
If this call returns PGRES_POLLING_FAILED, the connection procedure has
failed. If this call returns PGRES_POLLING_OK, the connection has
madhtr [EMAIL PROTECTED] writes:
cleary I am going to have to study this documentation more carefully ... So
... for simplicity's sake, If I just do the following, how do I get back
database does not exist ?
[ shrug... ] Your program works perfectly for me:
$ ./testit
PQerrorMessage(lpcn)
Ed L. wrote:
I ran a simple select-for-update test on 8.1.2 and was curious as
to why the semantics are what they are. Specifically, when you
have multiple select-for-update-limit queries on the same rows,
why are rows selected by the blocked query before knowing if
some of those rows
I have been able to spend some more time working on this and have made a bit
of progress.
I had the trigger set to run before insert; and the sql inside the trigger
needed to ref the new record in order to supply a value. I think that's why
the first try always applied a null value.
So I've
On 8/14/07, Phoenix Kiula [EMAIL PROTECTED] wrote:
*And* you can define compound foreign key constraints,
Thank you for this detailed explanation Alban. But I want to include
FK constraints on a table2 on a column in the referenced table1 where
column values are not unique.
I just want
- Original Message -
From: Tom Lane [EMAIL PROTECTED]
To: madhtr [EMAIL PROTECTED]
Cc: pgsql-general@postgresql.org
Sent: Tuesday, August 14, 2007 18:50
Subject: Re: [GENERAL] pqlib in c++: PQconnectStart PQconnectPoll
madhtr [EMAIL PROTECTED] writes:
cleary I am going to have to
On 8/14/07, Kenneth Downs [EMAIL PROTECTED] wrote:
RPK wrote:
I want to know whether MVCC has cons also. Is it heavy on resources? How
PGSQL MVCC relates with SQL Server 2005 new Snapshot Isolation.
Speaking as an end-user, I can give only one I've ever seen, which is
performance.
madhtr [EMAIL PROTECTED] writes:
From: Tom Lane [EMAIL PROTECTED]
... although it takes a good long while (several seconds) because of the
sleep(1) in the interaction with the postmaster.
Sleep(1) should be only 1/1000 of a second. I do that so I don't hammer the
processor with my while
On 15/08/07, Chris Browne [EMAIL PROTECTED] wrote:
[EMAIL PROTECTED] (Phoenix Kiula) writes:
I have a table with ten columns. My queries basically one column as
the first WHERE condition, so an index on that column is certain. But
the columns after that one vary depending on end-user's
Alvaro Herrera [EMAIL PROTECTED] writes:
Ed L. wrote:
I ran a simple select-for-update test on 8.1.2 and was curious as
to why the semantics are what they are. Specifically, when you
have multiple select-for-update-limit queries on the same rows,
why are rows selected by the blocked query
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
On 08/14/07 20:36, Merlin Moncure wrote:
[snip]
PostgreSQL wins in terms of better concurrency (especially in long
transactions or transactions that touch a lot of records), cheap
rollbacks, and all the advantages of a sophisticated locking
novnov [EMAIL PROTECTED] writes:
The function is meant to find a value via the internal sql, and then update
a column in the same tabled that invoked the trigger with that value. Is
that a non-workable scenario? IE, an after insert trigger can't modify a
column in the same table that was
On Tue, 2007-08-14 at 10:16 -0500, Scott Marlowe wrote:
On 8/14/07, Ow Mun Heng [EMAIL PROTECTED] wrote:
In MySql, I was using mysqlimport --replace which essentially provided
the means to load data into the DB, while at the same time, would
provide the necessary logic to replace the
Phoenix Kiula [EMAIL PROTECTED] writes:
Thank you so much! My only concern, probably a hangover from the MySQL
world, is that if I have 5 of 6 indices, what would that do to INSERT
and UPDATE performance if all these indices have to be updated? Is
updating individual indices faster than one
Ow Mun Heng [EMAIL PROTECTED] writes:
Ps : Is it this list's norm to have the OP/sender in the to list and
mailing list on the CC list?
Yes. If you don't like that you can try including a Reply-To: list
header in what you send to the list; or perhaps better, I think there's
a way to tell the
Thanks, triggers are new turf for me so nothing is quite obvious. I thought,
the after insert change is snuck in after the rec is created and via some
built in behavior allows mods to the rec without triggering a cascade. But
the reality makes sense and of course I can adjust my work to handle in
If this is only a PostgreSQL database server, don't limit the postgres user.
Don't tweak these limits unless you know exactly what you are doing.
Unfortunately, it is not. It has other applications. Including Apache
and so on. I tried not setting the ulimits at all, but it seems to be
required
Hi all-
I am evaluating databases for use in a large project that will hold image
data as blobs. I know, everybody says to just store pointers to files on the
disk...can't do it here...the images are of a confidential nature and access
to the database (and resulting client app) will be highly
On 8/14/07, Kirk Wythers [EMAIL PROTECTED] wrote:
I need some help with rewriting a query. I have a fairly complicated query
(for me anyway) that dumps daily climate data, filling in missing data with
monthly averages (one line per day).
I want to output monthly averages (one line per
On 8/14/07, Phoenix Kiula [EMAIL PROTECTED] wrote:
Thank you for this detailed explanation Alban. But I want to include
FK constraints on a table2 on a column in the referenced table1 where
column values are not unique.
I just want row data to be consistent for the same ID. Yes, this is
Hi,
I was wondering if any other database has language interface for different
programing languages such as C , perl , python etc the way postgresql has.
Thanks,
Jas
Hi,
I read a few lines about SP compilation in postgres
http://searchoracle.techtarget.com/originalContent/0,289142,sid41_gci1179016,00.html
*1. stored procedure compilation is transactional. *
*You can recompile a stored procedure on a live system, and only
transactions starting after that
The default values of a column during table definition do not accept
values generated by passing another column's value through my own
function. So I try to do this with a rule as follows. The name of my
function in this example is MYFUNCTION.
drop table test cascade;
create table test (id
On 8/14/07, Jasbinder Singh Bali [EMAIL PROTECTED] wrote:
I was wondering if any other database has language interface for different
programing languages such as C , perl , python etc the way postgresql has.
Assuming you mean stored procedures, Microsoft's SQL Server hosts the
CLR, which means
Hi,
Let me fine tune my question here. What I mean to say is the way we can
write stored procedures in C, perl etc in Postgres specifying the language
parameter at the end of stored procedure, compared to that, in SQL Server
2000 I've seen SP writing in pure SQL only.
Can you write Stored
Lim Berger [EMAIL PROTECTED] writes:
create table test (id serial primary key, nowd timestamp without time
zone, processed_id varchar(10));
create or replace rule test_ins as on insert to test
DO UPDATE test
SET processed_id = MYFUNCTION(NEW.id)
WHERE id = NEW.id
;
I think you are
So turning to Postgresql, can I get any recommendations, suggestions and
tips on blob handling in the database? The image sizes will be pretty
variable, from a few kilobytes to several hundred megabytes, so I need
something that will handle the various file sizes, hopefully transparently.
Jasbinder Singh Bali [EMAIL PROTECTED] writes:
Let me fine tune my question here. What I mean to say is the way we can
write stored procedures in C, perl etc in Postgres specifying the language
parameter at the end of stored procedure, compared to that, in SQL Server
2000 I've seen SP writing
On 8/14/07, Jasbinder Singh Bali [EMAIL PROTECTED] wrote:
Let me fine tune my question here. What I mean to say is the way we can
write stored procedures in C, perl etc in Postgres specifying the language
parameter at the end of stored procedure, compared to that, in SQL Server
2000 I've seen
On 8/14/07, Harpreet Dhaliwal [EMAIL PROTECTED] wrote:
Hi,
I read a few lines about SP compilation in postgres
http://searchoracle.techtarget.com/originalContent/0,289142,sid41_gci1179016,00.html
1. stored procedure compilation is transactional.
You can recompile a stored procedure on a
On 8/14/07, Tom Lane [EMAIL PROTECTED] wrote:
Jasbinder Singh Bali [EMAIL PROTECTED] writes:
Let me fine tune my question here. What I mean to say is the way we can
write stored procedures in C, perl etc in Postgres specifying the language
parameter at the end of stored procedure, compared
So you mean to say DDL statements can't be put in one single transaction in
Oracle ?
On 8/15/07, Scott Marlowe [EMAIL PROTECTED] wrote:
On 8/14/07, Harpreet Dhaliwal [EMAIL PROTECTED] wrote:
Hi,
I read a few lines about SP compilation in postgres
Harpreet Dhaliwal [EMAIL PROTECTED] writes:
I read a few lines about SP compilation in postgres
http://searchoracle.techtarget.com/originalContent/0,289142,sid41_gci1179016,00.html
Is this what the Transactional DDL feature of postgresql talks about ?
I'd say it's one very small aspect of
And this feature i.e. transactional DDL is not there in other major RDBMS
like sql server, oracle etc?
thanks
~Harpreet
On 8/15/07, Tom Lane [EMAIL PROTECTED] wrote:
Harpreet Dhaliwal [EMAIL PROTECTED] writes:
I read a few lines about SP compilation in postgres
Scott Marlowe [EMAIL PROTECTED] writes:
On 8/14/07, Tom Lane [EMAIL PROTECTED] wrote:
... I think we probably are unique in being so
aggressively agnostic about what the function language is. That's
not necessarily all good, as it's driven us to invent curiosities
like dollar-quoting to
On 8/14/07, Harpreet Dhaliwal [EMAIL PROTECTED] wrote:
So you mean to say DDL statements can't be put in one single transaction in
Oracle ?
You can put them in, but then they will cause the previous DMK to be
silently committed
---(end of
88 matches
Mail list logo
