On 7/01/2010 5:03 AM, shulkae wrote:
I am writing a shell script which runs as a cron entry. The objective
is to delete older records from postgresql DB.
I have thousands of records. What is the optimum number of records to
delete in one delete command
( my script will delete records in a loop
Hi all!
I tried this:
tmp1=# CREATE DOMAIN real as numeric;
CREATE DOMAIN
tmp1=# CREATE TABLE t1 ( r real );
CREATE TABLE
tmp1=# CREATE TABLE t2 ( r real );
CREATE TABLE
tmp1=# INSERT INTO t1 VALUES ( 0.01 );
INSERT 0 1
tmp1=# INSERT INTO t1 VALUES (
Hello,
We use PG 8.3. We use pg_dump and pg_restore overnight to create
copies of main database for reporting etc. One dump/restore runs at 9
PM, another at 11 PM.
Today I discovered that the restore at 11 PM failed to recreate a
foreign key constraint, because one row from master table was
Greg Smith g...@2ndquadrant.com writes:
If you're OK with the possibility of losing a measurement in the case of a
system crash
Then I'd say use synchronous_commit = off for the transactions doing
that, trading durability (the 'D' of ACID) against write
performances. That requires 8.3 at least,
One more bit of information. The master table has exactly 3 rows
missing. They were all inserted half a year ago within one minute and
no other rows have been inserted in between them.
Is it possible that we lost a consistent piece of data (like a block
or a page)? What can I do to track it down?
On 7 Jan 2010, at 24:12, Gauthier, Dave wrote:
thedb=# create table foo (col1 text, constraint chk check (col1 in
('a','b','c',null)));
CREATE TABLE
thedb=# insert into foo (col1) values ('xxx');
INSERT 0 1
H... I would have thought that this would have violated the constraint
On Wed, Jan 6, 2010 at 11:09 AM, Marius Pitigoi
marius.piti...@gmail.com wrote:
Hello,
Is there a way to protect psql source code? For example oracle has wrap
utility.
I want to deploy my DB on a hosting company server. But they can see my
functions code (they have root privileges) and this
Konrad Garus wrote:
We use PG 8.3. We use pg_dump and pg_restore overnight to create
copies of main database for reporting etc. One dump/restore runs at 9
PM, another at 11 PM.
Today I discovered that the restore at 11 PM failed to recreate a
foreign key constraint, because one row from
2010/1/7 Albe Laurenz laurenz.a...@wien.gv.at:
If that's really the case, it sounds like curruption.
Is there anything in the server logs?
I'm pretty sure it is corruption. With FK in place I don't see any
other way how it would be possible.
The log is too large to read through, but I
Le 07/01/2010 11:12, Konrad Garus a écrit :
Hello,
We use PG 8.3. We use pg_dump and pg_restore overnight to create
copies of main database for reporting etc. One dump/restore runs at 9
PM, another at 11 PM.
Today I discovered that the restore at 11 PM failed to recreate a
foreign key
So, question is: did you disable triggers sometime on the referenced table?
No, at least not intentionally. More information:
1. Missing are 3 rows added quickly one after another over half a
year ago. They were lost this week. That is the only corruption I am
aware of.
2. The problem is
Both suggestions (exclude the 'null' from the list, and include or col1 is
null) work.
And if I ever wanted to require that col1 is not null, just add that as a
separate constraint.
Thanks guys for the advise and explanation !
-Original Message-
From: Alban Hertroys
On Thu, Jan 7, 2010 at 1:44 AM, Yan Cheng Cheok ycch...@yahoo.com wrote:
Sorry if this question had been asked before. Although I had googled, but
find no answer.
I try to use C++, to iterate the array returned from stored procedure.
std::stringstream ss;
ss SELECT * FROM
Vincenzo Romano vincenzo.rom...@notorand.it writes:
I tried this:
tmp1=# CREATE DOMAIN real as numeric;
[ and got confused between this domain and the built-in real ]
It looks like to me this is a bug and also the documentation seems to confirm:
The domain name must be unique among the types
Dear list,
I am migrating an Oracle database to PostgreSQL and the last step I must
perform is to convert several Pro*C programs into PostgreSQL embedded SQL.
I work on AIX 5.3 TL9 64-bits, on which I have built PostgreSQL 8.3.8 in
64-bits too.
I am a newbie in Embedded SQL and I am facing
Heyho!
On Wednesday 06 January 2010 18.26:05 Joshua D. Drake wrote:
...
With this in mind: what can we tune to minimize diskspace (RAM-disk
space) usage?
[thanks to all for your input!]
Wow, here are some things but honestly Pg may not be the DB you are
looking for.
Well, parts of the
Hi all,
I have a deja vu or I had this very problem before.
Now I use 8.4.2 and it happened again.
After dropping a column from table, there is still entry in pg_attribute
fi...@la_dev=# select * from pg_attribute where attrelid = (select oid from
pg_class where relname='thetable') order by
Hi all.
I've put together the ideas I used to solve a problem of mine with
table partitioning.
A small article (no code yet) is here:
http://notsononsense.blogspot.com/2009/12/postgresql-different-approach-to-table.html
And I'd like to get some feedback from you.
Many thanks in advance.
--
I suggest that you use libpqxx. libpqxx already has a utility template
function called separated_list() that lives in the pqxx namespace for
converting std containers into array literals. It also has a function
template called from_string that lives in the same place. I imagine
it's implemented in
=?UTF-8?Q?Filip_Rembia=C5=82kowski?= plk.zu...@gmail.com writes:
INSERT INTO thetable ( ... ) VALUES ( ... );
ERROR: table row type and query-specified row type do not match
If you want any help with this you need to show a *complete* example
of how to produce this failure.
On 01/06/2010 08:49 PM, Greg Smith wrote:
Yan Cheng Cheok wrote:
The time taken to perform measurement per unit is in term of ~30
milliseconds. We need to record down the measurement result for every
single unit. Hence, the time taken by record down the measurement
result shall be far more
On Thu, Jan 7, 2010 at 11:30 AM, Peter Geoghegan
peter.geoghega...@gmail.com wrote:
I suggest that you use libpqxx. libpqxx already has a utility template
function called separated_list() that lives in the pqxx namespace for
converting std containers into array literals. It also has a function
Are Writable CTE queries defined by the SQL standard, or are they and extension?
--
Regards,
Richard Broersma Jr.
Visit the Los Angeles PostgreSQL Users Group (LAPUG)
http://pugs.postgresql.org/lapug
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your
I was in attendance at David Fetter's Writeable CTEs: The Next Big
Thing talk in Paris last November. He said that they (by which I
assume he meant himself and Marko, the author of writeable CTEs in
postgres) were trying to get them into the SQL standard.
Regards,
Peter Geoghegan
--
Sent via
Richard Broersma richard.broer...@gmail.com wrote:
Are Writable CTE queries defined by the SQL standard, or are they and
extension?
They are cool, and i hope, in 8.5 ;-)
Andreas
--
Really, I'm not out to destroy Microsoft. That will just be a completely
unintentional side effect.
dear sir
could you please help ?
I have postgres installed in mac and want to connect it from window XP, I get
error, can you please help what is needed to get connected to mac server
postgres ?
Server [localhost]: 10.1.1.9
Database [postgres]: viewods
Port [5432]: 5433
Username
In response to Shu Ho sueh...@hotmail.com:
I have postgres installed in mac and want to connect it from window XP, I get
error, can you please help what is needed to get connected to mac server
postgres ?
Server [localhost]: 10.1.1.9
Database [postgres]: viewods
Port [5432]: 5433
We had WAL Log shipping (warm standby) working fine under 8.1.4 but under 8.3.7
we can't get the slave to come up properly. Nothing has changed in our process
with regard to start_backup, rsync, stop_backup, bring up the warm standby
server in continuous recovery mode, but the failover DB
Hello all, im having a problem with a crashed database and im looking
for help to try recover any data from my last backup.
Thats the situation, i found this error on postgresql logs 15 days ago:
invalid memory alloc request size 4294967295
Then, 10 minutes later i got on my logs:
:
Hi All -
Is there way to the list of indexes created for all the tables in
postgres? can you please help
Regards
Venkat
SELECT * from pg_stat_user_indexes;
On 01/07/2010 02:23 PM, akp geek wrote:
Hi All -
Is there way to the list of indexes created for all the tables
in postgres? can you please help
Regards
Venkat
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make
Filip Rembiałkowski wrote:
After dropping a column from table, there is still entry in pg_attribute
fi...@la_dev=# select * from pg_attribute where attrelid = (select oid
from pg_class where relname='thetable') order by attnum desc limit 1;
-[ RECORD 1 ]-+--
Tom Lane wrote:
Vincenzo Romano vincenzo.rom...@notorand.it writes:
I tried this:
tmp1=# CREATE DOMAIN real as numeric;
[ and got confused between this domain and the built-in real ]
It looks like to me this is a bug and also the documentation seems to
confirm:
The domain
Russell Smith mr-r...@pws.com.au writes:
Really, I can't see how quoting an identifier should change the
behaviour or resolved type.
REAL is not an identifier. It's a keyword. In fact, it's a reserved
word according to SQL99. So if we were going to do anything about this,
it would be to
I try to have the following stored procedure, to help me create tables, if the
table does not exists (Is this a good practice by the way?)
if not exists(select * from information_schema.tables where table_name =
'MYTABLE') then
RAISE NOTICE 'table not there yet.';
CREATE TABLE MYTABLE
(
Yan Cheng Cheok wrote:
I try to have the following stored procedure, to help me create tables, if the
table does not exists (Is this a good practice by the way?)
if not exists(select * from information_schema.tables where table_name =
'MYTABLE') then
RAISE NOTICE 'table not there yet.';
On 7/01/2010 11:27 PM, Adrian von Bidder wrote:
Heyho!
On Wednesday 06 January 2010 18.26:05 Joshua D. Drake wrote:
...
With this in mind: what can we tune to minimize diskspace (RAM-disk
space) usage?
[thanks to all for your input!]
Wow, here are some things but honestly Pg may not be the
On 8/01/2010 3:47 AM, Samuel Abreu de Paula wrote:
Hello all, im having a problem with a crashed database and im looking
for help to try recover any data from my last backup.
Thats the situation, i found this error on postgresql logs 15 days ago:
invalid memory alloc request size 4294967295
On Wed, Dec 30, 2009 at 6:39 PM, Seb splu...@gmail.com wrote:
CREATE RULE footwear_nothing_upd AS
ON UPDATE TO footwear DO INSTEAD NOTHING;
CREATE RULE footwear_newshoelaces_upd AS
ON UPDATE TO footwear
WHERE NEW.sl_name OLD.sl_name AND OLD.sl_name IS NULL
DO
INSERT INTO
Hi All -
I have query in production and test. The tables in both the
environment has the same structure ,indexes and constraints. But the in the
test and the prod the explain plan is totally different. In test environment
the query is taking long time and noticed that indexes are
If you do a:
VACUUM FULL ANALYZE
for both production and for test and then do an EXPLAIN on your queries,
what do the plans look like?
From: pgsql-general-ow...@postgresql.org
[mailto:pgsql-general-ow...@postgresql.org] On Behalf Of akp geek
Sent: Thursday, January 07, 2010 8:11 PM
To:
On Thu, Jan 7, 2010 at 9:11 PM, akp geek akpg...@gmail.com wrote:
Hi All -
I have query in production and test. The tables in both the
environment has the same structure ,indexes and constraints. But the in the
test and the prod the explain plan is totally different. In test
I did that and the Explain look different
Regards
On Thu, Jan 7, 2010 at 11:13 PM, Dann Corbit dcor...@connx.com wrote:
If you do a:
VACUUM FULL ANALYZE
for both production and for test and then do an EXPLAIN on your queries,
what do the plans look like?
*From:*
Post the results here
From: akp geek [mailto:akpg...@gmail.com]
Sent: Thursday, January 07, 2010 8:30 PM
To: Dann Corbit
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Index question on postgres
I did that and the Explain look different
Regards
On Thu, Jan 7, 2010 at
the explain from both enviroments ??? need to be posted.
just one quick question. Why would the index I have created not being used?
Regards
On Thu, Jan 7, 2010 at 11:34 PM, Dann Corbit dcor...@connx.com wrote:
Post the results here
*From:* akp geek [mailto:akpg...@gmail.com]
*Sent:*
On Thu, Jan 7, 2010 at 10:04 PM, akp geek akpg...@gmail.com wrote:
the explain from both enviroments ??? need to be posted.
just one quick question. Why would the index I have created not being used?
Regards
Better explain analyze than plain eplain.
If the retrieval of data by seq scan is
Currently, I try to call a stored procedure with void returned type.
PGresult *res = PQexec(this-getConnection(), SELECT * FROM
create_tables());
if (PQresultStatus(res) != PGRES_COMMAND_OK)
{
PQclear(res);
return false;
}
Since the returned type of stored
Hello all,
I have the following procedure. I wish it will return a single row result to
caller, after I insert the value (as the row contains several auto generated
fields), without perform additional SELECT query.
According to
48 matches
Mail list logo
