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
http://wischner.blogspot.com/2009/03/creating-stored-procedure-fun
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 proc
On Thu, Jan 7, 2010 at 10:04 PM, akp geek 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 deemed "cheaper" th
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 wrote:
> Post the results here
>
>
>
> *From:* akp geek [mailto:akpg...@gmail.com]
> *Sent:* Thursday, Janu
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 11:13
I did that and the Explain look different
Regards
On Thu, Jan 7, 2010 at 11:13 PM, Dann Corbit 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:* pgsql-general-ow...@postgres
On Thu, Jan 7, 2010 at 9:11 PM, akp geek 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 environment
> the
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: pgsql-
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 no
On Wed, Dec 30, 2009 at 6:39 PM, Seb 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 shoelaces (sh_i
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 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
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.';
CRE
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
(
"va
Russell Smith 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 reject the OP's CREAT
Tom Lane wrote:
> Vincenzo Romano 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
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 ]-+--
attreli
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)
Hi All -
Is there way to the list of indexes created for all the tables in
postgres? can you please help
Regards
Venkat
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:
: processo
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 won't
In response to Shu Ho :
>
> 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 [postg
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 [postgr
Richard Broersma 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. (Linu
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 p
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
On Thu, Jan 7, 2010 at 11:30 AM, Peter Geoghegan
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
> template called "from_s
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
=?UTF-8?Q?Filip_Rembia=C5=82kowski?= 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.
regards, tom
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 i
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.
--
Vin
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 att
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
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 sev
Vincenzo Romano 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 and domains existin
On Thu, Jan 7, 2010 at 1:44 AM, Yan Cheng Cheok 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 get_array_test()";
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 [mailto:dal...@
> 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 row
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
2010/1/7 Albe Laurenz :
> 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 haven't spotted anything
su
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 fro
On Wed, Jan 6, 2010 at 11:09 AM, Marius Pitigoi
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 is what I want to a
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
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?
Greg Smith 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, and will not fsync
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 missi
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 (
0.00
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
48 matches
Mail list logo
