Re: [GENERAL] calling a function over several rows

[Adam Rich]

Merlin Moncure wrote:

On Tue, Nov 17, 2009 at 1:02 AM, Adam Rich  wrote:

Hello,
There is an existing function which takes an integer and returns a record.
 I need to call this function with every integer in a table.  Is there a
simple shortcut for doing this?

I'm looking for something like:

select f.*
from function(t.value) f, table t


select (f).* from (select function(t.value) as f from table t) q;

merlin



Thanks, that's perfect, and much faster than the one I came up with in 
the interim:


select (f(t.value)).* from table t;



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

[GENERAL] plperl: spi_query_prepared/spi_fetchrow versus spi_exec_prepared: memory?

[Nathan Jahnke]

hi all,

having some memory leak issues with my app and spi_exec_prepared.
checking the docs:

"Normally, spi_fetchrow should be repeated until it returns undef,
indicating that there are no more rows to read. The cursor is
automatically freed when spi_fetchrow returns undef. If you do not
wish to read all the rows, instead call spi_cursor_close to free the
cursor. Failure to do so will result in memory leaks."

... which is all well and good, but what about the non-cursor-creating
version spi_exec_prepared which fetches all rows into memory right off
the bat? i would think that doing e.g.:

my $test = spi_exec_prepared(spi_prepare('select something from table
where uid=$1', 'int'), $ref->{uid})->{rows};

... would free the ram of those rows when $test goes out of scope but
this is not the case for me on 8.3 (debian) tonight. even explicitly
setting $test = '' also leaves that data in ram. however this:

my $test = spi_query_prepared(spi_prepare('select something from table
where uid=$1', 'int'), $ref->{uid})->{rows};
while (defined (my $row = spi_fetchrow($test))) {}

... DOES free the ram as the docs suggest. so what's the recommended
way to use spi_exec_prepared() while being able to free the ram that
it allocates for its result set? sometimes i just want everything and
don't want to bother with a cursor.


thanks,

nathan

-- 
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] calling a function over several rows

[Pavel Stehule]

2009/11/17 Adam Rich :
> Hello,
> There is an existing function which takes an integer and returns a record.
>  I need to call this function with every integer in a table.  Is there a
> simple shortcut for doing this?
>
> I'm looking for something like:
>
> select f.*
> from function(t.value) f, table t
>

Hello,

attention, relation databases isn't spreadsheet. There isn't any
shortcut. You can write function based on information_schema.colums
that helps with generating SQL statement.

Regards
Pavel Stehule

> Thanks,
> Adam
>
>
>
>
>
> --
> 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] calling a function over several rows

[Merlin Moncure]

On Tue, Nov 17, 2009 at 1:02 AM, Adam Rich  wrote:
> Hello,
> There is an existing function which takes an integer and returns a record.
>  I need to call this function with every integer in a table.  Is there a
> simple shortcut for doing this?
>
> I'm looking for something like:
>
> select f.*
> from function(t.value) f, table t

select (f).* from (select function(t.value) as f from table t) q;

merlin

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

[GENERAL] calling a function over several rows

[Adam Rich]

Hello,
There is an existing function which takes an integer and returns a 
record.  I need to call this function with every integer in a table.  Is 
there a simple shortcut for doing this?


I'm looking for something like:

select f.*
from function(t.value) f, table t

Thanks,
Adam





--
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] specifying a password on the cmd line for db connect

[Ashesh Vashi]

Hi Dave,

You can always use the environment variable PGPASSWORD to do that.
Though - it is not recommended to use for security reason. :(
Please follow the link for the details.
http://www.postgresql.org/docs/8.3/static/libpq-envars.html

--
Thanks & Regards,

Ashesh Vashi
EnterpriseDB INDIA - The Enterprise Postgres
Company

"Make everything as simple as possible, but not simpler..." -- Albert
Einstein
 "We are what our thoughts have made us; so take care about what you think."
-- Swami Vivekananda


On Tue, Nov 17, 2009 at 4:42 AM, Raymond O'Donnell  wrote:

> On 16/11/2009 23:05, Raymond O'Donnell wrote:
> > On 16/11/2009 22:51, Gauthier, Dave wrote:
> >> --username foo  --password fee
>
> [snip]
>
> > There are no options such as thesewhat you do instead is use a
>
> Actually, to correct myself, there *are* these options - but the
> --password option just forces a password prompt - it doesn't actually
> allow you to specify the password.
>
> Have a look at psql --help for all the options.
>
> Ray.
>
> --
> Raymond O'Donnell :: Galway :: Ireland
> r...@iol.ie
>
> --
> 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 full search text on a shared hosting web site using 8.1.x

[Raymond C. Rodgers]

John DeSoi wrote:

On Nov 16, 2009, at 2:12 PM, Raymond Rodgers wrote:
  
a2hosting.com shows "unlimited" plans which include Postgres 8.4 (tsearch already included) for about $5/month. If you really need an older version, they might be willing to add your account to an older shared server. I know they used to have contrib tsearch installed with older versions.



http://www.a2hosting.com/services/web-hosting/

  
It's actually my preference to have a newer version, preferably at least 
8.3.x. But at the time that I was doing my research (3-4 weeks ago), A2 
didn't seem to have anything priced comparably to HostNine. Their 
executive package is indeed a bit cheaper than HostNine, and I'm now 
thinking about whether I'll brave the potential headaches of canceling 
with HostNine. At the very least, if I do, I should have an easier time 
moving my sites over: they're currently running on 8.3.x on my doomed 
hosting account, and it was a pain in the hindquarters to move just a 
single bare bones site on over to 8.1.x even without the full text 
search


Thanks for the tip!
Raymond

--
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] Corrupt indices on already-dropped table (could not open relation with OID ...)

[Tom Lane]

Craig de Stigter  writes:
> Somehow we've ended up with a few corrupt indices in our database.

What PG version is this exactly?  Do you have any idea how you got into
this state?  (Database crashes, system crashes, whatever?)  We've seen
a few similar reports before, but never with enough clarity to identify
the bug, if it is a bug.

> I'm a bit hesitant to try the fix mentioned at the following URL since it
> involves deleting things from system tables:
> http://javadave.blogspot.com/2005/06/could-not-open-relation-in-postgresql.html

It'd be safer to dump and reload the database.  However, given that you
already removed the underlying table, I don't see a reason to be
terribly concerned about the consistency of the entries about this
index.

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] Corrupt indices on already-dropped table (could not open relation with OID ...)

[Craig de Stigter]

Hi folks

Somehow we've ended up with a few corrupt indices in our database. We've
previously dropped the table they were on, but the indices are still there
(kind of):


Trying to drop the indices gives us:

drop index "v_038e_GEOMETRY";

ERROR:  could not open relation with OID 9590980



Looking up that index in pg_class:

select oid, relname from pg_class where relname = 'v_038e_GEOMETRY';

   oid   |   relname

-+-

 9590993 | v_038e_GEOMETRY



And looking up those OIDs in pg_depend:

select * from pg_depend where objid = 9590993;

 classid |  objid  | objsubid | refclassid | refobjid | refobjsubid |
> deptype

-+-+--++--+-+-

1259 | 9590993 |0 |   1259 |  9590980 |   3 | a

1259 | 9590993 |0 |   2616 |20506 |   0 | n



But that table doesn't exist anymore (that's okay, we dropped it earlier):

select * from pg_class where oid = 9590980 or relname = 'v_038e';

(0 rows)



Restarting the database didn't help, unfortunately.

I'm a bit hesitant to try the fix mentioned at the following URL since it
involves deleting things from system tables:
http://javadave.blogspot.com/2005/06/could-not-open-relation-in-postgresql.html


Any suggestions for a nicer approach? Or can someone who knows tell me if
its okay to follow the instructions at that url, without breaking anything?

Thanks

Craig de Stigter

-- 
Koordinates Ltd
PO Box 1604, Shortland St, Auckland, New Zealand
Phone +64-9-966 0433 Fax +64-9-969 0045
Web http://www.koordinates.com

Re: [GENERAL] "money" binary representation

[Andrew Chernow]

I'm trying to read "money" field using PQgetvalue (PostgreSQL 8.3.7). The
function returns 9 bytes, smth like 0h 0h 0h 0h 0h 0h 14h 0h 0h, for the


Huh...  You mean 8 bytes, right?

--
Andrew Chernow
eSilo, LLC
every bit counts
http://www.esilo.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] "money" binary representation

[Merlin Moncure]

On Mon, Nov 16, 2009 at 3:02 AM, Jasen Betts  wrote:
> On 2009-11-16, Konstantin Izmailov  wrote:
>> --000e0cd5d09230ff7d04787526aa
>> Content-Type: text/plain; charset=ISO-8859-1
>>
>> I'm trying to read "money" field using PQgetvalue (PostgreSQL 8.3.7). The
>> function returns 9 bytes, smth like 0h 0h 0h 0h 0h 0h 14h 0h 0h, for the
>> value '$50.2'. I could not find description anywhere on how to convert the
>> binary data into, for example, a double precision number.
>
> money is a 64 bit integer representing a number of cents, it appears
> to be big-endian byte order on ypur system
>
> it is deprecated and should not be used in new applications, use some
> sort of numeric instead.
>
>> Would you please help me find a method of converting binary "money" data
>> into a double precision?
>
> floating point is not recomended for financial calculations, but
> dividing the integer value by 100.0 should get you there.

if you are reading/writing binary data to/from postgres, you should
consider using libpqtypes:

PGmoney m;
res = PQexecf(conn, "INSERT INTO foo(m) values(%money) returning m", m);
PQgetf(res, 0, "#money", "m", &m);  /* # notation pulls field by name */

boy, this question sure seems to come up an awful lot!

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] specifying a password on the cmd line for db connect

[Raymond O'Donnell]

On 16/11/2009 23:05, Raymond O'Donnell wrote:
> On 16/11/2009 22:51, Gauthier, Dave wrote:
>> --username foo  --password fee

[snip]

> There are no options such as thesewhat you do instead is use a

Actually, to correct myself, there *are* these options - but the
--password option just forces a password prompt - it doesn't actually
allow you to specify the password.

Have a look at psql --help for all the options.

Ray.

-- 
Raymond O'Donnell :: Galway :: Ireland
r...@iol.ie

-- 
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] specifying a password on the cmd line for db connect

[Raymond O'Donnell]

On 16/11/2009 22:51, Gauthier, Dave wrote:
> Hi:
> 
> Is there a way to require and specify a password at the cmd line for a 
> connect?  At first glance...
> 
> --username foo  --password fee
> 
> would seem to be what I want.  But it just prompts me for a password (after 
> interpreting "fee" as the DB name).

Hi there,

There are no options such as thesewhat you do instead is use a
pgpass file:

  http://www.postgresql.org/docs/8.4/static/libpq-pgpass.html

Ray.

-- 
Raymond O'Donnell :: Galway :: Ireland
r...@iol.ie

-- 
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] specifying a password on the cmd line for db connect

[Greg Smith]

Gauthier, Dave wrote:


Hi:

 

Is there a way to require and specify a password at the cmd line for a 
connect?


You don't want to do that because any user on the system can see the 
command, and thus the password.  You want to use a .pgpass file instead:


http://www.postgresql.org/docs/8.4/interactive/libpq-pgpass.html

--
Greg Smith2ndQuadrant   Baltimore, MD
PostgreSQL Training, Services and Support
g...@2ndquadrant.com  www.2ndQuadrant.com

[GENERAL] specifying a password on the cmd line for db connect

[Gauthier, Dave]

Hi:

Is there a way to require and specify a password at the cmd line for a connect? 
 At first glance...

--username foo  --password fee

would seem to be what I want.  But it just prompts me for a password (after 
interpreting "fee" as the DB name).

Thanks In Advance

Re: [GENERAL] Update on Insert

[Dann Corbit]

> -Original Message-
> From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general-
> ow...@postgresql.org] On Behalf Of Sebastian Feher
> Sent: Monday, November 16, 2009 10:41 AM
> To: pgsql-general@postgresql.org
> Subject: [GENERAL] Update on Insert
> 
> Hi Everyone,
> 
> Given a table "Items" with a PK "item1" and "Qty" - a numeric column
> I'd like to define a way in Postgres to insert when item11 doesn't
> exist already in "Items" and update the Qty by adding the new quantity
> to the existent when the item11 exists. What is a good approach and
> where should I be looking for details. Thanks.

That is called MERGE.  Merge is not currently supported:
http://www.postgresql.org/docs/8.4/interactive/unsupported-features-sql-
standard.html

You could set the transaction level to Serializable, start a
transaction, check for existence, if the row exists perform an update,
else perform an insert. Then commit.



-- 
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] Full Text Search 101?

[Jonathan]

Hi!

Thanks again for the help.  Just wanted to let you all know that it
seems like we had an issue with our install of Postgres 8.3 on our
development server.  I installed 8.4 on my own local machine and the
indexing works as it should.

Jonathan

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

[GENERAL] Update on Insert

[Sebastian Feher]

Hi Everyone,

Given a table "Items" with a PK "item1" and "Qty" - a numeric column
I'd like to define a way in Postgres to insert when item11 doesn't
exist already in "Items" and update the Qty by adding the new quantity
to the existent when the item11 exists. What is a good approach and
where should I be looking for details. Thanks.

Sebastian

-- 
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] passing parameters to multiple statements

[manitou-sig]

Konstantin Izmailov wrote:

> I'm planning to use multiple statements via libpq. Before starting coding
> I'm trying to understand are there any limitations on passing parameters.
> E.g. would the following work:
>   PQexecParams(conn, "BEGIN;INSERT INTO tbl VALUES($1,$2);SELECT
> lastval();SELECT * INTO AUDIT FROM (SELECT $3, 'tbl action',
> lastval());COMMIT;", 3, ...);

No, because PQexecParams doesn't accept multiple SQL statements.

Best regards,
-- 
Daniel
PostgreSQL-powered mail user agent and storage: http://www.manitou-mail.org

-- 
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 full search text on a shared hosting web site using 8.1.x

[John DeSoi]

On Nov 16, 2009, at 2:12 PM, Raymond Rodgers wrote:

> Aside from this issue, they're the best I've found for the least amount of 
> money in terms of bandwidth, disk space, and PostgreSQL support. I'd happily 
> continue to use my current, more expensive, hosting provider if they weren't 
> phasing out support for compiling your own applications with their system, 
> which means that I'll be losing PostgreSQL support because they only support 
> MySQL officially.
> 
> I went through the list of the hosting providers on the PostgreSQL site, and 
> while the old adage "you get what you pay for" might be applicable in this 
> instance, they were the only one that I could really afford at this point in 
> time.


a2hosting.com shows "unlimited" plans which include Postgres 8.4 (tsearch 
already included) for about $5/month. If you really need an older version, they 
might be willing to add your account to an older shared server. I know they 
used to have contrib tsearch installed with older versions.


http://www.a2hosting.com/services/web-hosting/



John DeSoi, Ph.D.





-- 
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] createdb errors and more

[Scott Marlowe]

On Mon, Nov 16, 2009 at 9:55 AM, Reno Bladergroen
 wrote:
> Today I restarted to install postgres from scratch, because I have a lot of 
> installation problems. To summarize my system:
> I have two xubuntu logins: a "superuser" and postgres. The latter one is a 
> user with basic privileges.
> I installed pgsql according to the manual, generated a data folder, changed 
> ownership to postgres, switched user postgres and initialized the database. 
> starting the database is also successful (status says running).
> But now: when I use the command createdb test, I get the error "can't connect 
> to database postgres: could not connect to server: no such file or directory, 
> Is the server running locally and accepting  etc."

Ooops, you left out the part where you tell us what you did.  Seeing
your createdb switches etc would help a lot.  Better to give us too
much info than not enough.

-- 
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] createdb errors and more

[Reno Bladergroen]

eh... no I installed once from source, because the package-manager failed to 
install the main application (PostgreSQL 8.3). Somehow it could not configure 
(after a few minutes the make command ended and an error popped up). Therefore 
I had to start from source, which did work.
Today after getting this error message time after time I did start all over 
again and executed the ./configure-make-make install sequence again. Can that 
be harmful? Should I uninstall completely before doing that?

Unless this is an unstable system, I did get it running! The end-application is 
connecting and working.

Thanks for your help,

Reno



**
This email and any attachments may contain confidential or privileged
information and is intended for the addressee only. If you are not the
intended recipient, please immediately notify us by email or telephone and
delete the original email and attachments without using, disseminating or
reproducing its contents to anyone other than the intended recipient. The
azM shall not be liable for the incorrect or incomplete transmission of this
email or any attachments, nor for unauthorized use by its employees.
**


-- 
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 full search text on a shared hosting web site using 8.1.x

[Tom Lane]

John R Pierce  writes:
> can't you install the contrib module as a regular database user into the 
> database you own?

No, it would take superuser privs which he hasn't got.  More, even with
superuser privs it would need the tsearch2.so library to be present on
disk, which it likely isn't if they aren't supporting contrib.  And
(which is actually a somewhat legitimate reason for them to deny
support) it's fairly difficult to do anything useful with tsearch2
without the ability to write/edit its config files, which requires
outside-the-database filesystem access.

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] [HACKERS] Update on Insert

[Thom Brown]

2009/11/16 Robert Haas :
> On Mon, Nov 16, 2009 at 1:31 PM, SebiF  wrote:
>> Hi Everyone,
>>
>> Given a table "Items" with a PK "item1" and "Qty" - a numeric column
>> I'd like to define a way in Postgres to insert when item11 doesn't
>> exist already in "Items" and update the Qty by adding the new quantity
>> to the existent when the item11 exists. What is a good approach and
>> where should I be looking for details. Thanks.
>>
>> Sebastian
>
> This is not a -hackers question; please use pgsql-general instead.
>
> Also see example 38-2 here:
> http://www.postgresql.org/docs/current/static/plpgsql-control-structures.html
>
> ...Robert
>

You could try something like the following:

CREATE FUNCTION update_items(item_value TEXT, qty_value INTEGER)
RETURNS BOOL AS $$
DECLARE item_rec RECORD;
BEGIN
SELECT INTO item_rec * FROM items WHERE item = item_value;

IF item_rec.item IS NULL THEN
INSERT INTO items (item, qty) VALUES (item_value, qty_value);
RAISE NOTICE 'added';
RETURN true;
ELSE
UPDATE items SET qty = qty + qty_value WHERE item = item_value;
RAISE NOTICE 'updated';
RETURN true;
END IF;
END;
$$ LANGUAGE plpgsql

Then use:

SELECT update_items('monkey',9);

You could probably also create an updateable view with a rule applied
or something.

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] Need full search text on a shared hosting web site using 8.1.x

[John R Pierce]

Raymond Rodgers wrote:
I just signed up for web hosting with HostNine which offers PostgreSQL 
8.1.x, which doesn't have the full text search support in the default 
installl. I requested that they run the tsearch2.sql from the 
contributed files on the server to provide me with those capabilities 
since I don't have super user access to the database server, but they 
just denied my request. Are there any work arounds to provide this 
support or something similar within PostgreSQL? I'd hate to have to 
cobble together an inferior basic search capability in PHP if I didn't 
have to...


can't you install the contrib module as a regular database user into the 
database you own?




--
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 full search text on a shared hosting web site using 8.1.x

[Raymond Rodgers]

On Mon, Nov 16, 2009 at 1:51 PM, Tom Lane  wrote:

> Raymond Rodgers  writes:
> > I just signed up for web hosting with HostNine which offers PostgreSQL
> > 8.1.x, which doesn't have the full text search support in the default
> > installl. I requested that they run the tsearch2.sql from the contributed
> > files on the server to provide me with those capabilities since I don't
> have
> > super user access to the database server, but they just denied my
> request.
>
> If they don't sell what you want to buy (ie, access to a reasonably
> modern version of Postgres), go where they do sell it.
>
>regards, tom lane
>
Aside from this issue, they're the best I've found for the least amount of
money in terms of bandwidth, disk space, and PostgreSQL support. I'd happily
continue to use my current, more expensive, hosting provider if they weren't
phasing out support for compiling your own applications with their system,
which means that I'll be losing PostgreSQL support because they only support
MySQL officially.

I went through the list of the hosting providers on the PostgreSQL site, and
while the old adage "you get what you pay for" might be applicable in this
instance, they were the only one that I could really afford at this point in
time.

Raymond

Re: [GENERAL] Need full search text on a shared hosting web site using 8.1.x

[Tom Lane]

Raymond Rodgers  writes:
> I just signed up for web hosting with HostNine which offers PostgreSQL
> 8.1.x, which doesn't have the full text search support in the default
> installl. I requested that they run the tsearch2.sql from the contributed
> files on the server to provide me with those capabilities since I don't have
> super user access to the database server, but they just denied my request.

If they don't sell what you want to buy (ie, access to a reasonably
modern version of Postgres), go where they do sell it.

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] createdb errors and more

[Tom Lane]

Reno Bladergroen  writes:
> I have two xubuntu logins: a "superuser" and postgres. The latter one is a 
> user with basic privileges.
> I installed pgsql according to the manual, generated a data folder, changed 
> ownership to postgres, switched user postgres and initialized the database. 
> starting the database is also successful (status says running).
> But now: when I use the command createdb test, I get the error "can't connect 
> to database postgres: could not connect to server: no such file or directory, 
> Is the server running locally and accepting  etc."
> When I switch user back to "superuser" I can start the server, add users etc. 
> in pgAdmin3. But I need to be able to do this from the command line.

Stop the postmaster so that createdb fails for both logins, and compare
the error messages --- are they pointing to the same socket file?  I
suspect JD's diagnosis is right: you probably have got two postgres
installations (or parts of two at least) with different ideas of where
the communication socket should be.

> When I reboot, the database is not automatically started.

"man chkconfig" might help you with this one.  Linux systems are not
normally made to auto-start services unless they're very specifically
requested.

By and large, it's a lot less painful to use a preconfigured package
than to try to install from source.

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] Need full search text on a shared hosting web site using 8.1.x

[Raymond Rodgers]

I just signed up for web hosting with HostNine which offers PostgreSQL
8.1.x, which doesn't have the full text search support in the default
installl. I requested that they run the tsearch2.sql from the contributed
files on the server to provide me with those capabilities since I don't have
super user access to the database server, but they just denied my request.
Are there any work arounds to provide this support or something similar
within PostgreSQL? I'd hate to have to cobble together an inferior basic
search capability in PHP if I didn't have to...

Thank you,
Raymond

Re: [GENERAL] What is the correct way to extract values from an int8 array in SPI?

[Boszormenyi Zoltan]

Tom Lane írta:
> Boszormenyi Zoltan  writes:
>   
>> //ids =
>> PointerGetDatum(PG_DETOAST_DATUM(SPI_getbinval(prod_inv->vals[0],
>> prod_inv->tupdesc, 1, &isnull)));
>> 
>
> well, for one thing, you probably want DatumGetPointer ...

You chose the commented out line to comment on. :-)
The original line was after this one:

ids = SPI_getbinval(prod_inv->vals[0], prod_inv->tupdesc, 1, &isnull);


I only experimented with whether I need to detoast the array value.

>  for another,
> you shouldn't really be converting to Pointer here at all, since the
> next line expects ids to still be a Datum.  On some platforms you can
> get away with being fuzzy about the distinction between Datum and
> Pointer, but it doesn't surprise me in the least that such code would
> fail elsewhere.  Try paying more attention to compiler warnings ;-)
>   

I did. :-) PointerGetDatum(PG_DETOAST_DATUM(SPI_getbinval(...)))
doesn't emit any warnings.

> The lack of any checks for null-ness scares me, too.
>   Aside from the
> multiple places where you're just plain ignoring an isnull return flag,
> there's the risk that the array might contain one or more nulls,


I omitted this particular check because:
- ids bigint[] NOT NULL, and
- the code that builds the content of the array ensures
  that no array member can be NULL. They are bigint IDs
  from another table. :-)

>  in
> which case you can't address the last element that way (even if that
> element itself isn't null).
>   

Yeah, this is what bothers me.

$ psql -p 5433 index_test
psql (8.4.1)
Type "help" for help.

index_test=# select array_length(ids,1) from
product.t_product_inv_titleonly where word='cpu';
 array_length
--
   96
(1 row)

index_test=# select ids from product.t_product_inv_titleonly where
word='cpu';


 



 


   
ids  


 



 


--
--
--
--
--
-
 
{29767643,29783831,33973788,33994384,33966944,33974483,33945574,33988076,33957605,33985034,29050215,33925825,33961012,29066655,33955860,33981152,33990118,33937422,33
972534,33923080,33921945,33979786,33926521,33983828,33980602,33932253,33926012,33925643,40361238,42814197,45923261,33933417,33952470,33988350,33930668,33925627,339799
81,33937362,31250473,35083034,33958934,33946597,33948953,33993455,33987994,33923724,33934644,33961183,34905945,33931220,33973198,33979613,33993878,31973668,47835781,4
7835782,47836159,47866522,47866523,47867628,33943565,33966303,45072269,33955440,33959714,33948651,33977798,30113741,33975105,33943434,33932791,33954807,33922152,33971
756,27401475,27407609,27401410,27405102,33620032,33621234,33624659,30116651,33966940,30116815,30121547,30113990,30115882,33958841,30123948,33953821,33929316,47373326,
47374380,47374458,30123436,33930912}
(1 row)


You can see that the above array doesn't have NULLs.
But this has debug output has revealed the real problem:

ids = SPI_getbinval(prod_inv->vals[0], prod_inv->tupdesc, 1, &isnull);
n_ids = DatumGetInt32(DirectFunctionCall2(array_length, ids,
Int32GetDatum(1)));
ids_arr = DatumGetArrayTypeP(ids);
ids_data = (Datum *) ARR_DATA_PTR(ids_arr);

/* Set up the initial indexes for binary search */
idx_min = 0;
idx_max = n_ids - 1;
idx_mid = (idx_max + idx_min) / 2;

elog(NOTICE, "n_ids %d idx_min %d idx_max %d idx_mid %d", n_

Re: [GENERAL] createdb errors and more

[Joshua D. Drake]

On Mon, 2009-11-16 at 17:55 +0100, Reno Bladergroen wrote:
> Today I restarted to install postgres from scratch, because I have a lot of 
> installation problems. To summarize my system:
> I have two xubuntu logins: a "superuser" and postgres. The latter one is a 
> user with basic privileges.
> I installed pgsql according to the manual, generated a data folder, changed 
> ownership to postgres, switched user postgres and initialized the database. 
> starting the database is also successful (status says running).
> But now: when I use the command createdb test, I get the error "can't connect 
> to database postgres: could not connect to server: no such file or directory, 
> Is the server running locally and accepting  etc."
> When I switch user back to "superuser" I can start the server, add users etc. 
> in pgAdmin3. But I need to be able to do this from the command line.
> PLEASE help, I am getting really frustrated with this system, it already took 
> me 2 weeks and still didn't get it right.
> 
> Another thing I can't get done: I created the postgresql init script in 
> /etc/init.d/
> When I reboot, the database is not automatically started. Do I need to login 
> in the linux postgres account to let this happen? The database should also 
> start in the background even when I login into another account?
> Should the data dir only be owned by postgres or the entire /usr/local/pgsql 
> dir??
> 
> Again, hopefully somebody can push me in the right direction.

Sounds to me that you have postgres from xubuntu and postgres from
source installed. Don't use source unless you have to.


Joshua D. Drake



> 
> Thanks, reno
> 
> 
> 
> **
> This email and any attachments may contain confidential or privileged
> information and is intended for the addressee only. If you are not the
> intended recipient, please immediately notify us by email or telephone and
> delete the original email and attachments without using, disseminating or
> reproducing its contents to anyone other than the intended recipient. The
> azM shall not be liable for the incorrect or incomplete transmission of this
> email or any attachments, nor for unauthorized use by its employees.
> **
> 
> 


-- 
PostgreSQL.org Major Contributor
Command Prompt, Inc: http://www.commandprompt.com/ - 503.667.4564
Consulting, Training, Support, Custom Development, Engineering
If the world pushes look it in the eye and GRR. Then push back harder. - 
Salamander


-- 
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] createdb errors and more

[Rikard Bosnjakovic]

On Mon, Nov 16, 2009 at 17:55, Reno Bladergroen
 wrote:

> I installed pgsql according to the manual, generated a data folder, changed 
> ownership to postgres, switched user postgres and initialized the database. 
> starting the database is also successful (status says running).

[...]

I assume you are talking about the steps similiar to the ones listed
under 14.1 Short Version here;
http://www.postgresql.org/docs/8.0/interactive/installation.html. If
so, peek around the logfile produced for postmaster and see if you can
find anything useful in there.

To me, the problems sounds like the initdb-command failed somehow,
since you get the message "could not connect to server", but it could
also mean that the server actually did not start. What happens if you
do "telnet localhost 5432"? If you get a reply, the server is atleast
up and running.


--
- Rikard

-- 
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] Postgres issues "Not enough storage is available to complete this operation" exception

[Bill Moran]

"Maria Cianci"  wrote:
> 
>I am running on a Windows XP machine, 4GB of RAM and over 26GB of
> free hard-disk an application that all it does is insert records into
> the Postgres 8.3 database.  When the database is approx 17GB Postgres
> issues the "Not enough storage is available to complete this operation"
> and refuses to insert records.

What method are you using to gauge the size of the database?  Keep in
mind that PG needs disk space for things other than the DB itself, WAL
logs, for example.  If PG doesn't have space to create more WAL logs,
it can't process data changes.

> I normally shut down and restart the
> application and data collection continues normally until it reaches the
> 26GB and issues the same error: "Not enough storage is available to
> complete this operation."  I'm very puzzled to say the least.

Really?  If you're using all 26G, what other error would you expect?

I find it odd that it would have problems when there are nearly 10G free,
though.  What methods are you using to measure DB size, and disk usage?

Keep in mind that unless you check disk usage at the exact moment the
error occurs, you don't really know what the usage was, as Postgres may
have cleaned up some files after the error was detected.  I recommend
using SNMP or some similar method to record disk usage on an ongoing
basis, then correlate that data with timing of the errors.  Based on
past experience, I'd be willing to bet that you really _are_ running
out of disk space at the time the error is reported.  I believe that
large transactions can cause this, because they take up a lot of disk
space during processing that is then freed once the transaction is either
completed or rolled back.  Does your application do large transactions?

-- 
Bill Moran
http://www.potentialtech.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] createdb errors and more

[Reno Bladergroen]

Today I restarted to install postgres from scratch, because I have a lot of 
installation problems. To summarize my system:
I have two xubuntu logins: a "superuser" and postgres. The latter one is a user 
with basic privileges.
I installed pgsql according to the manual, generated a data folder, changed 
ownership to postgres, switched user postgres and initialized the database. 
starting the database is also successful (status says running).
But now: when I use the command createdb test, I get the error "can't connect 
to database postgres: could not connect to server: no such file or directory, 
Is the server running locally and accepting  etc."
When I switch user back to "superuser" I can start the server, add users etc. 
in pgAdmin3. But I need to be able to do this from the command line.
PLEASE help, I am getting really frustrated with this system, it already took 
me 2 weeks and still didn't get it right.

Another thing I can't get done: I created the postgresql init script in 
/etc/init.d/
When I reboot, the database is not automatically started. Do I need to login in 
the linux postgres account to let this happen? The database should also start 
in the background even when I login into another account?
Should the data dir only be owned by postgres or the entire /usr/local/pgsql 
dir??

Again, hopefully somebody can push me in the right direction.

Thanks, reno



**
This email and any attachments may contain confidential or privileged
information and is intended for the addressee only. If you are not the
intended recipient, please immediately notify us by email or telephone and
delete the original email and attachments without using, disseminating or
reproducing its contents to anyone other than the intended recipient. The
azM shall not be liable for the incorrect or incomplete transmission of this
email or any attachments, nor for unauthorized use by its employees.
**


-- 
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] Postgres issues "Not enough storage is available to complete this operation" exception

[Thom Brown]

2009/11/16 Maria Cianci :
> All,
>
>
>
>    I am running on a Windows XP machine, 4GB of RAM and over 26GB of free
> hard-disk an application that all it does is insert records into the
> Postgres 8.3 database.  When the database is approx 17GB Postgres issues the
> “Not enough storage is available to complete this operation” and refuses to
> insert records.  I normally shut down and restart the application and data
> collection continues normally until it reaches the 26GB and issues the same
> error: “Not enough storage is available to complete this operation.”  I’m
> very puzzled to say the least.  The Postgres configuration is attached.  For
> some reason Postgres would no load if shared_buffers > 900MB.
>
>
>
> I should say that my Windows is running with the /3GB flag.
>
>
>
> Thanks in advance for any help you can offer.
>

From looking at a previous post, you'd need to install/reinstall MDAC
2.6: http://archives.postgresql.org/pgadmin-support/2002-05/msg00063.php

Regards

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] Postgres issues "Not enough storage is available to complete this operation" exception

[Maria Cianci]

All, 

 

   I am running on a Windows XP machine, 4GB of RAM and over 26GB of
free hard-disk an application that all it does is insert records into
the Postgres 8.3 database.  When the database is approx 17GB Postgres
issues the "Not enough storage is available to complete this operation"
and refuses to insert records.  I normally shut down and restart the
application and data collection continues normally until it reaches the
26GB and issues the same error: "Not enough storage is available to
complete this operation."  I'm very puzzled to say the least.  The
Postgres configuration is attached.  For some reason Postgres would no
load if shared_buffers > 900MB.  

 

I should say that my Windows is running with the /3GB flag.

 

Thanks in advance for any help you can offer.

 

Maria Cianci

Software Engineer

CAE USA Professional Services

CONSIM Group

3501 Quadrangle Blvd., Suite 271

Orlando, FL 32817

maria.cia...@cae.com

CAE: (407)745-2609

 


Privileged/Confidential Information may be contained in this message. If you 
are not the addressee indicated in this message (or responsible for delivery of 
the message to such person) you may not use, copy, disclose or deliver this 
message to anyone. If you have received this message in error, kindly notify 
the sender by reply email or at the phone number listed above, and then delete 
this message.


postgresql.conf.8.3
Description: postgresql.conf.8.3

-- 
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] What is the correct way to extract values from an int8 array in SPI?

[Alvaro Herrera]

Boszormenyi Zoltan wrote:
> Hi,
> 
> I am using this code on 8.4/8.5, which works on 64-bit,
> but segfaults on 32-bit Linux:
> 

I'm not sure but perhaps this patch could help you.  It may be a bit outdated.

-- 
Alvaro Herrerahttp://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support
Index: doc/src/sgml/spi.sgml
===
RCS file: /home/alvherre/Code/cvs/pgsql/doc/src/sgml/spi.sgml,v
retrieving revision 1.65
diff -c -p -r1.65 spi.sgml
*** doc/src/sgml/spi.sgml	5 Aug 2009 19:31:50 -	1.65
--- doc/src/sgml/spi.sgml	9 Oct 2009 20:16:58 -
*** char * SPI_getnspname(Relation palloc,
 repalloc, or SPI utility functions (except for
 SPI_copytuple,
+SPI_copydatum,
 SPI_returntuple,
 SPI_modifytuple, and
 SPI_palloc) are made in this context.  When a
*** HeapTuple SPI_copytuple(HeapTuple 
  
+ 
+  
+   SPI_copydatum
+   3
+  
+ 
+  
+   SPI_copydatum
+   make a copy of a datum in the upper executor context
+  
+ 
+  SPI_copydatum
+ 
+  
+ 
+ Datum SPI_copydatum(Datum value, bool typByVal, int typLen)
+ 
+  
+ 
+  
+   Description
+ 
+   
+SPI_copydatum makes a copy of a datum in the
+upper executor context.
+   
+  
+ 
+  
+   Arguments
+ 
+   
+
+ Datum value
+ 
+  
+   datum to be copied
+  
+ 
+
+ 
+
+ bool typByVal
+ 
+  
+   whether the type of the datum is passed by value
+  
+ 
+
+ 
+
+ int typLen
+ 
+  
+   length of the type
+  
+ 
+
+ 
+   
+  
+ 
+  
+   Return Value
+ 
+   
+the copied datum; NULL only if
+value is NULL
+   
+  
+ 
+ 
+ 
+ 
  
   
SPI_returntuple
Index: src/backend/executor/spi.c
===
RCS file: /home/alvherre/Code/cvs/pgsql/src/backend/executor/spi.c,v
retrieving revision 1.209
diff -c -p -r1.209 spi.c
*** src/backend/executor/spi.c	2 Oct 2009 17:57:30 -	1.209
--- src/backend/executor/spi.c	9 Oct 2009 20:35:03 -
*** SPI_copytuple(HeapTuple tuple)
*** 615,620 
--- 615,635 
  	return ctuple;
  }
  
+ Datum
+ SPI_copydatum(Datum value, bool typByVal, int typLen)
+ {
+ 	Size		len;
+ 	void	   *tmp;
+ 	Datum		retval;
+ 
+ 	len = datumGetSize(value, typByVal, typLen);
+ 	tmp = SPI_palloc(len);
+ 	memcpy(tmp, DatumGetPointer(value), len);
+ 	retval = PointerGetDatum(tmp);
+ 
+ 	return retval;
+ }
+ 
  HeapTupleHeader
  SPI_returntuple(HeapTuple tuple, TupleDesc tupdesc)
  {
Index: src/include/executor/spi.h
===
RCS file: /home/alvherre/Code/cvs/pgsql/src/include/executor/spi.h,v
retrieving revision 1.72
diff -c -p -r1.72 spi.h
*** src/include/executor/spi.h	11 Jun 2009 14:49:11 -	1.72
--- src/include/executor/spi.h	9 Oct 2009 20:00:19 -
*** extern bool SPI_plan_is_valid(SPIPlanPtr
*** 98,103 
--- 98,104 
  extern const char *SPI_result_code_string(int code);
  
  extern HeapTuple SPI_copytuple(HeapTuple tuple);
+ extern Datum SPI_copydatum(Datum value, bool typByVal, int typLen);
  extern HeapTupleHeader SPI_returntuple(HeapTuple tuple, TupleDesc tupdesc);
  extern HeapTuple SPI_modifytuple(Relation rel, HeapTuple tuple, int natts,
  int *attnum, Datum *Values, const char *Nulls);
Index: src/pl/plpgsql/src/pl_exec.c
===
RCS file: /home/alvherre/Code/cvs/pgsql/src/pl/plpgsql/src/pl_exec.c,v
retrieving revision 1.248
diff -c -p -r1.248 pl_exec.c
*** src/pl/plpgsql/src/pl_exec.c	6 Aug 2009 20:44:31 -	1.248
--- src/pl/plpgsql/src/pl_exec.c	9 Oct 2009 20:36:01 -
*** plpgsql_exec_function(PLpgSQL_function *
*** 438,452 
  			 * into upper executor memory context.
  			 */
  			if (!fcinfo->isnull && !func->fn_retbyval)
! 			{
! Size		len;
! void	   *tmp;
! 
! len = datumGetSize(estate.retval, false, func->fn_rettyplen);
! tmp = SPI_palloc(len);
! memcpy(tmp, DatumGetPointer(estate.retval), len);
! estate.retval = PointerGetDatum(tmp);
! 			}
  		}
  	}
  
--- 438,445 
  			 * into upper executor memory context.
  			 */
  			if (!fcinfo->isnull && !func->fn_retbyval)
! estate.retval = SPI_copydatum(estate.retval, false,
! 			  func->fn_rettyplen);
  		}
  	}
  

-- 
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] Experience with many schemas vs many databases

[Peter Hunsberger]

On Sun, Nov 15, 2009 at 3:45 PM, undisclosed user
 wrote:
> Backing up data by user is required for my solution. A lot of times, users
> screw up and they want to rollback to a previous state.

In that case, being able to revert the state of an application should
be part of the application (and database) design and not rely on
database: history tables, etc...

-- 
Peter Hunsberger

-- 
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] Comparing bit in an integer field

[Amitabh Kant]

On Mon, Nov 16, 2009 at 7:53 PM, A. Kretschmer <
andreas.kretsch...@schollglas.com> wrote:

> In response to Amitabh Kant :
>
> test=# select * from t1;
>  a1
> 
>  0
>  12
>  8
>  0
>  1
>  10
>  7
>  19
> (8 rows)
>
> test=*# select a1 from t1 where (a1>>2)::bit = B'1';
>  a1
> 
>  12
>  7
> (2 rows)
>
>
> Regards, Andreas
>

Thanks. That should solve my problem.

Amitabh

Re: [GENERAL] safelly erasing dirs/files

[Scott Marlowe]

On Mon, Nov 16, 2009 at 2:37 AM, Jasen Betts  wrote:
> On 2009-11-14, Joao Ferreira gmail  wrote:
>> ok. thx all for the explanation
>>
>> my problem is I a heavilly bloated pg database that has just filled up
>> the partition
>>
>> data is in /var/pgsql/.. and /var is 100% full...
>>
>> vacuum/reindex is saying: I can't do it cause I have no space :(
>>
>> how do I un-bloat the /var partition ? this is why I was considering
>> rm 
>
> If ubuntu or debian  /var/cache/apt/archives/* can safely be removed
> and will often free up a lot of space, (or on redhat
> /var/cache/yum/*/packages/*)
>
> If you installed from source you may have several daily log files you
> can remove, redhat seems also to keep the logs around
> thse files are often found in /var/lib/pgsql/data/pg_log and should
> contain ascii text. Debian and ubuntu put them in /var/log/postgres
> and use logrotate to trim them, but there maybe other files in /var/log
> that can be truncated or erased to free up space.

Also note that if you're on ext2/ext3 and your partitions were set up
with some amount of reserved space for root you can free up some space
for a minute by using tune2fs:

sudo tune2fs -m 0 /dev/sda1

then turn it back to reserved when you're done:

sudo tune2fs -m 2 /dev/sda1

-- 
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] What is the correct way to extract values from an int8 array in SPI?

[Tom Lane]

Boszormenyi Zoltan  writes:
> //ids =
> PointerGetDatum(PG_DETOAST_DATUM(SPI_getbinval(prod_inv->vals[0],
> prod_inv->tupdesc, 1, &isnull)));

well, for one thing, you probably want DatumGetPointer ... for another,
you shouldn't really be converting to Pointer here at all, since the
next line expects ids to still be a Datum.  On some platforms you can
get away with being fuzzy about the distinction between Datum and
Pointer, but it doesn't surprise me in the least that such code would
fail elsewhere.  Try paying more attention to compiler warnings ;-)

The lack of any checks for null-ness scares me, too.  Aside from the
multiple places where you're just plain ignoring an isnull return flag,
there's the risk that the array might contain one or more nulls, in
which case you can't address the last element that way (even if that
element itself isn't null).

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] Comparing bit in an integer field

[Thom Brown]

2009/11/16 A. Kretschmer :
> In response to Amitabh Kant :
>> Hello
>>
>> I need to compare the bit values of a integer field in my table. For 
>> example, I
>> have a table called "t1" with just one field "a1" having following values:
>>
>> a1
>> ==
>> 0
>> 12
>> 8
>> 0
>> 1
>> 10
>> 7
>> 19
>>
>> I am trying to fetch all records where the 3rd binary bit is 1, which from 
>> the
>> above example should be 12 (1100) and 7 (0111). If I convert the 
>> values
>> to binary and store it as string, I can easily compare them using substr, but
>> would rather like to retain the integer field. I have tried using the get_bit
>> function, but it seems my understanding of the function is not correct.
>
> test=# select * from t1;
>  a1
> 
>  0
>  12
>  8
>  0
>  1
>  10
>  7
>  19
> (8 rows)
>
> test=*# select a1 from t1 where (a1>>2)::bit = B'1';
>  a1
> 
>  12
>  7
> (2 rows)
>

Ah, bit-shifting.  Told you there'd be a cleaner way ;)

-- 
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] Comparing bit in an integer field

[A. Kretschmer]

In response to Amitabh Kant :
> Hello
> 
> I need to compare the bit values of a integer field in my table. For 
> example, I
> have a table called "t1" with just one field "a1" having following values:
> 
> a1
> ==
> 0
> 12
> 8
> 0
> 1
> 10
> 7
> 19
> 
> I am trying to fetch all records where the 3rd binary bit is 1, which from the
> above example should be 12 (1100) and 7 (0111). If I convert the 
> values
> to binary and store it as string, I can easily compare them using substr, but
> would rather like to retain the integer field. I have tried using the get_bit
> function, but it seems my understanding of the function is not correct.

test=# select * from t1;
 a1

  0
 12
  8
  0
  1
 10
  7
 19
(8 rows)

test=*# select a1 from t1 where (a1>>2)::bit = B'1';
 a1

 12
  7
(2 rows)


Regards, Andreas
-- 
Andreas Kretschmer
Kontakt:  Heynitz: 035242/47150,   D1: 0160/7141639 (mehr: -> Header)
GnuPG: 0x31720C99, 1006 CCB4 A326 1D42 6431  2EB0 389D 1DC2 3172 0C99

-- 
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] Comparing bit in an integer field

[Thom Brown]

2009/11/16 Amitabh Kant :
> Hello
>
> I need to compare the bit values of a integer field in my table. For
> example, I have a table called "t1" with just one field "a1" having
> following values:
>
> a1
> ==
> 0
> 12
> 8
> 0
> 1
> 10
> 7
> 19
>
> I am trying to fetch all records where the 3rd binary bit is 1, which from
> the above example should be 12 (1100) and 7 (0111). If I convert the
> values to binary and store it as string, I can easily compare them using
> substr, but would rather like to retain the integer field. I have tried
> using the get_bit function, but it seems my understanding of the function is
> not correct.
>
> I would appreciate any help in this regard.
>
>
> Amitabh
>

There's probably a clean way of doing this, but you could do:

SELECT a1
FROM t1
WHERE (a1::bit(10) & 4::bit(10))::int = 4;

 a1

 12
  7
(2 rows)

Regards

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] Comparing bit in an integer field

[Amitabh Kant]

Hello

I need to compare the bit values of a integer field in my table. For
example, I have a table called "t1" with just one field "a1" having
following values:

a1
==
0
12
8
0
1
10
7
19

I am trying to fetch all records where the 3rd binary bit is 1, which from
the above example should be 12 (1100) and 7 (0111). If I convert the
values to binary and store it as string, I can easily compare them using
substr, but would rather like to retain the integer field. I have tried
using the get_bit function, but it seems my understanding of the function is
not correct.

I would appreciate any help in this regard.


Amitabh

Re: [GENERAL] Customize the install directory of the postgres DB

[Eric Comeau]

"Greg Smith"  wrote in message 
news:4afde758.1050...@2ndquadrant.com...
> Tom Lane wrote:
>> The real problem that I think the OP hasn't considered is whether
>> his "bundled" RPM package isn't going to conflict with a preinstalled
>> postgresql RPM.  Relocating the RPM, either dynamically as you suggest
>> or by just changing the install paths while building it, isn't a very
>> palatable solution since e.g. you really want libpq.so in /usr/lib,
>> psql in /usr/bin/, etc.
>>
> It sounds like they really do want all of those things to be installed in 
> a subdirectory of their app, so I don't see a problem with them being 
> there instead of the standard locations.  As long as they know how to run 
> psql etc., not having them in the global PATH might be a feature rather 
> than a problem.  The only detail I'm aware of they may not have considered 
> is that a subdirectory install has the potential for the binaries to not 
> be able to find their associated libraries, so they might either have to 
> add those to the system loader configuration or set LD_LIBRARY_PATH before 
> calling database binaries.  Ideally you'd find them via rpath or something 
> so this isn't an issue, but it's easy to miss that the first time you make 
> a change like this.
>

We started out using pre-built PG group RPMs, then we went to building from 
src and custom built source RPMs (based on the PG group ones), changing the 
spec a bit, and finally settling on tar balling the PG install.

We install everything under one common directory  "db" under our 
applications home directory. We create a softlink to point to a directory 
for specific PG version (easier for migrations required for PG upgrades)

 ls -l db
total 28
drwxrwxr-x  3 root dtm  4096 Nov 14 04:49 .
drwxrwxr-x 16 root dtm  4096 Nov 13 23:05 ..
lrwxrwxrwx  1 postgres postgres   29 Nov 14 04:49 pgsql -> 
/usr/signiant/dds/db/pgsql_81
drwxr-xr-x 10 postgres postgres 4096 Oct 23 11:08 pgsql_81


Everything goes into the specific PG version directory;

ls -l
total 80
drwxr-xr-x 10 postgres postgres 4096 Oct 23 11:08 .
drwxrwxr-x  3 root dtm  4096 Nov 14 04:49 ..
drwxr-xr-x  2 postgres postgres 4096 Jul 10 13:59 bin
drwxr-xr-x  2 postgres postgres 4096 Nov 12 14:47 conf
drwx-- 10 postgres postgres 4096 Nov 14 04:54 data
drwxr-xr-x  3 postgres postgres 4096 Jul 10 13:59 doc
drwxr-xr-x  6 postgres postgres 4096 Jul 10 13:59 include
drwxr-xr-x  3 postgres postgres 4096 Jul 10 13:59 lib
drwxr-xr-x  4 postgres postgres 4096 Jul 10 13:59 man
drwxr-xr-x  3 postgres postgres 4096 Jul 10 13:59 share

To handle the shared libraries issues we build our apps (and PG) to look for 
the PG shared libraries in a specific location /usr/ddspostgres which our 
installer creates a soft link to the specific version

ls -l /usr/ddspost*
lrwxrwxrwx 1 root root 29 Nov 14 04:49 /usr/ddspostgres -> 
/usr/signiant/dds/db/pgsql_81

ldd on the psql we build looks for the shared library in /usr/dds/postgres;

 ldd psql
libpq.so.4 => /usr/ddspostgres/lib/libpq.so.4 (0x2aaad000)
libz.so.1 => /usr/lib64/libz.so.1 (0x0032b740)
libreadline.so.5 => /usr/lib64/libreadline.so.5 (0x2acd2000)
libtermcap.so.2 => /lib64/libtermcap.so.2 (0x0032b700)
libcrypt.so.1 => /lib64/libcrypt.so.1 (0x0032b840)
libresolv.so.2 => /lib64/libresolv.so.2 (0x0032b980)
libnsl.so.1 => /lib64/libnsl.so.1 (0x0032b800)
libdl.so.2 => /lib64/libdl.so.2 (0x0032b5c0)
libm.so.6 => /lib64/libm.so.6 (0x0032b600)
libc.so.6 => /lib64/libc.so.6 (0x0032b580)
/lib64/ld-linux-x86-64.so.2 (0x0032b540)


Our app thens to run on a server by itself in the wild, but we have run into 
a few installs with conflicts with pre-installed PostgreSQL, and issues with 
using  /usr/bin/psql instead of the psql utility in our install in certain 
scripts.

We have not yet had to run on a seperate port (that I am aware of), we still 
use the default port so we really haven't run into installs that required 
running two versions of PostgreSQL on the same server at the same time.

Eric 



-- 
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] [pgeu-general] pgday.eu

[Thom Brown]

2009/11/16 Dave Page :
> Thanks for working on these Thom.
>
No problem, although I'm disappointed with the output.  A bit of
planning before I left the UK and it would have been better.  Oh well,
better than nothing.

-- 
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] [pgeu-general] pgday.eu

[Dave Page]

Thanks for working on these Thom.

/D

On 11/16/09, Thom Brown  wrote:
> 2009/11/15 Thom Brown :
>> 2009/11/14 Thom Brown :
>>> 2009/11/14 Thom Brown 

 Mr Fetter has allowed me to post his lightning talk on lightning talks:
 http://vimeo.com/7602006
 Thom
>>>
>>> Harald's lightning talk also available with his
>>> permission: http://vimeo.com/7610987
>>> Thom
>>
>> Sorry, I've only just noticed that I'd accidently set Harald's video
>> from password-protected to completely private.  Fixed now.
>>
>> Thom
>>
>
> Koen Marten's lightning talk video now given the go-ahead:
> http://vimeo.com/7603429
>
> Thom
>


-- 
Dave Page
EnterpriseDB UK: http://www.enterprisedb.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] Voting: "pg_ctl init" versus "initdb"

[Zdenek Kotala]

Greg Smith píše v ne 15. 11. 2009 v 22:16 -0500:
> Tom Lane wrote: 
> > Simon Riggs  writes:
> >   
> > > If we did add an extra option then the option would be "initdb" not
> > > "init". It would take us all years to remove all evidence of the phrase
> > > "initdb" from the mailing lists and our minds.
> > > 
> > 
> > "init" is already embedded in various packagers' initscripts.  And
> > I thought the entire point of this proposal was that we could expunge
> > knowledge of initdb from users' minds. 
> Exactly.  I think the best transition design would be to make "initdb"
> and "init" both work.

"initdb" sounds me now better then "init", but to have both is
technically not problem. But question is if it is less confusing than
have only one of them.

Just a note that we already have WAL/XLOG or
postgres/postmaster/frontend/backend.

Zdenek


-- 
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] [pgeu-general] pgday.eu

[Thom Brown]

2009/11/15 Thom Brown :
> 2009/11/14 Thom Brown :
>> 2009/11/14 Thom Brown 
>>>
>>> Mr Fetter has allowed me to post his lightning talk on lightning talks:
>>> http://vimeo.com/7602006
>>> Thom
>>
>> Harald's lightning talk also available with his
>> permission: http://vimeo.com/7610987
>> Thom
>
> Sorry, I've only just noticed that I'd accidently set Harald's video
> from password-protected to completely private.  Fixed now.
>
> Thom
>

Koen Marten's lightning talk video now given the go-ahead:
http://vimeo.com/7603429

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] Voting: "pg_ctl init" versus "initdb"

[Zdenek Kotala]

Tom Lane píše v so 14. 11. 2009 v 11:22 -0500:
> Zdenek Kotala  writes:
> > Because there is doubt if someone else want this I would like to ask
> > here for your opinion. There are following options:
> 
> > 1) Yeah I like pg_ctl init
> 
> > "pg_ctl  init" will be preferred method and initdb will
> > disappear from usr/bin in the future.
> 
> > 2) Good, but keep initdb as well
> 
> > pg_ctl init and initdb stays forever
> 
> > 3) Do not touch my lovely initdb
> 
> > pg_ctl init is nonsense, initdb is only correct way.
> 
> You have listed them in reverse preference order ;-)

Maybe because I'm sitting on opposite hemisphere :-)  

> The only people who would actually care about this are packagers
> who think they can get away with taking initdb out of $PATH.
> If you believe that you can get away with that, you can do it today
> without any help from pg_ctl.  (Your theory presumably is that only
> one place in the initscript needs to know about it, and that one place
> could just as easily invoke initdb with an explicit path to wherever.)
> If you don't believe that you can get away with hiding initdb out of
> sight, then this patch is useless to you.

init script is not only one place when you need initdb. init script can
do it for you but often you need to setup correct locale. And admins
need to init database manually. And after that they want to have command
for it in default path. 

Another advantage of pg_ctl is that it is easy to extend it to cope with
more postgres versions and calls appropriate version of postgres or
initdb.

> 
> (BTW, have you actually tried moving initdb?  I wonder how well the
> relative-path logic for finding SHAREDIR etc is going to cope.)

libexecdir is not used.  find_other_exec() is little bit stupid. It
finds only binaries in the same directory. I guess It should look into
bindir and libexecdir as well.

when I'm thinking about it postgres and initdb should be installed into
libexecdir instead of bindir. For example sshd is in /usr/lib/sshd/ on
solaris.

> So I find the patch pretty useless.  But it's also pretty harmless,
> so long as it doesn't extend to the idea that we'd actually hide
> initdb in a default installation; at that point you're going to start
> hitting stiff resistance.

I supposed to use libexecdir for installation. If packager wants to hide
it than he can set libexecdir to another place. If not initdb will stay
in bindir.

Zdenek




-- 
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] Experience with many schemas vs many databases

[undisclosed user]

The app is very similar to wordpress MU. Each user has the same schema but
different data. The app uses the same codebase for every user. Users do not
have direct access to data. Currently, the DB is 90% r / 10% w and about
80GB MyISAM. Most of the queries are simple (75%)...the rest are joins
(25%). I am using myisam but I have too many concurrency and table crash
issues...  Mysql Fulltext search is horrible and causes a lot of
lockupstsearch2 seems like a good solution for us.

Basically, I want:
1. Good concurrency / decent performance
2. Data integrity
3. Fast Search
4. Ability to backup per user

Backing up data by user is required for my solution. A lot of times, users
screw up and they want to rollback to a previous state.

If I were to do a database per user, the backup/restore would be very
straight-forward. I believe backup/restore procedure is similar for schemas
(let me know if I am wrong here)? If I were to do a single schema/database,
is it possible to get data per user and back it up? Select user rows, copy
to a temp table/db, backup?

Thanks,
Frank



On Sun, Nov 15, 2009 at 1:11 PM, John R Pierce  wrote:

> undisclosed user wrote:
>
>> If I were to switch to a single DB/single schema format shared among all
>> users , how can I backup each user individually?
>>
>
> depending on how many tables, etc, I suppose you could use a seperate
> series of SELECT statements ...
> but if this is a requirement, it certainly puts constraints on how you
> organize your data.   without a much deeper knowlege of your application,
> data, and requirements, its kind of hard to give any sort of
> recommendations.   you mentioned myISAM, so I gather this data isn't at all
> transactional, nor is relational integrity a priority.
>
>
>
>
>
>
>
>
>

Re: [GENERAL] Voting: "pg_ctl init" versus "initdb"

[Loyal]

On Nov 14, 8:07 am, zdenek.kot...@sun.com (Zdenek Kotala) wrote:
> Hi all,
>
> 
> Because there is doubt if someone else want this I would like to ask
> here for your opinion. There are following options:
>
> 1) Yeah I like pg_ctl init
>
>         "pg_ctl  init" will be preferred method and initdb will
>         disappear from usr/bin in the future.
>
> 2) Good, but keep initdb as well
>
>         pg_ctl init and initdb stays forever
>
> 3) Do not touch my lovely initdb
>
>         pg_ctl init is nonsense, initdb is only correct way.
>
>         Thanks for your response
>
>                 Zdenek
> 

I would be in the group that says 3,2,1.  I just don't see a
big advantage to what you are suggesting.

Loyal

-- 
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] Config help

[Lew]

BuyAndRead Test wrote:

This is a virtual server, so I could give it as much as 8 GB of memory if
this will give much higher performance. What should shared_buffere be 
set to

if I use 8 GB, as much as 4 GB?


John R Pierce wrote:
I'd keep it around 1-2GB shared_buffers, and let the rest of the memory 
be used as file system cache.  postgres works quite happily that way.


From what I understand, database tuning is one of the Dark Arts.  PG is 
unique in that it's enterprise-grade but that standard settings work well 
across a wide range of usage scenarios.  If you are dealing with unusually 
large numbers of connections and/or unusually large working sets, I'm guessing 
as you approach terabyte-scale dbs and up, it pays to go to even larger 
shared_buffers and work_mem and do other arcane tuning magic.


--
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] Experience with many schemas vs many databases

[Loyal]

On Nov 15, 1:07 pm, lovetodrinkpe...@gmail.com (undisclosed user)
wrote:
> If I were to switch to a single DB/single schema format shared among all
> users , how can I backup each user individually?
>
> Frank
> 

I would love to understand why that would be a requirement.  I would
much
prefer backing up one database/schema.  If I needed to restore a
single user
I then have options such as restoring to my test database then
extracting
that single user's data, though it is still unclear to me why that
would be a
requirement.  If it is so single users can get a backup their own
data, I would
do it using a generic script or function.  Since your users would see
their own
views only that should work fine.

Loyal

-- 
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] safelly erasing dirs/files

[Jasen Betts]

On 2009-11-14, Joao Ferreira gmail  wrote:
> ok. thx all for the explanation
>
> my problem is I a heavilly bloated pg database that has just filled up
> the partition
>
> data is in /var/pgsql/.. and /var is 100% full...
>
> vacuum/reindex is saying: I can't do it cause I have no space :(
>
> how do I un-bloat the /var partition ? this is why I was considering
> rm 

If ubuntu or debian  /var/cache/apt/archives/* can safely be removed 
and will often free up a lot of space, (or on redhat
/var/cache/yum/*/packages/*)

If you installed from source you may have several daily log files you
can remove, redhat seems also to keep the logs around 
thse files are often found in /var/lib/pgsql/data/pg_log and should
contain ascii text. Debian and ubuntu put them in /var/log/postgres 
and use logrotate to trim them, but there maybe other files in /var/log
that can be truncated or erased to free up space.


-- 
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] dumping parts of a database

[A. Kretschmer]

In response to Malm Paul :
> Hi List,
> I have a database with two tables header and idata, they are connected with an
> conn_ID.
> I would like to dump header and the connected idata tables with a certain
> conn_ID. This I would like to import to the same type of database on another
> PostgreSQL server.
>  
> I would be gratefull if someone could tell me how I dump those tables and how
> do I import them in another database?

You can use COPY.

Just COPY ( select ... ) to '/path/to/file' to export and
COPY from ... to import that.


Andreas
-- 
Andreas Kretschmer
Kontakt:  Heynitz: 035242/47150,   D1: 0160/7141639 (mehr: -> Header)
GnuPG: 0x31720C99, 1006 CCB4 A326 1D42 6431  2EB0 389D 1DC2 3172 0C99

-- 
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] re-using RETURNING

[Jasen Betts]

On 2009-11-12, A. Kretschmer  wrote:
> Hi,
>
> just to be sure, it is still (8.4) not possible to use RETURNING within an
> other INSERT?

not in pure SQL, but it should be possible in PLPGSQL etc.


-- 
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] Raise functionality

[Jasen Betts]

On 2009-11-05, Sam Mason  wrote:
> On Thu, Nov 05, 2009 at 08:24:24AM -0600, Michael Gould wrote:
>> We want to control from our application how to handle certain exceptions. I
>> believe that Raise is the functionality that we want to use.  The
>> documentation is a little light on what happens on the client side.
>
> That's because it's up to the client to decide what to do.  You'll need
> to look at the documentation of whatever library/code you're using
> to talk to PG.  PG just aborts the transaction for anything apart
> from NOTIFY and hence your client will just see the transaction/query
> failing.  How you disentangle this is up to your code and how they with
> your drivers.

NOTICE, LOG, and DEBUG events are non-terminating, only EXCEPTION cancels the
transaction. NOTIFY is something completely different.
there is an option that must be set to make the events visible, I
forget what it is.

look up PQsetNoticeProcessor

At work we exploit notices to control our application's GUI.


-- 
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] Backups

[Jasen Betts]

On 2009-11-08, Bret  wrote:
>
> I need to back up a production database every night
> on FreeBSD 7.2, running Postgresql 8.3.
>
> Any good backup tips I should be aware of.

I backup using pgdump and move the dumps offsite using 

rsync --compress 

overwriting a copy of yesterdays backup

in this way I get better than 20:1 compression 

once offsite the backups are compressed and rotated,

I keep 6 dailys and 6 weeklys,


-- 
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] Absolute value of intervals

[Jasen Betts]

On 2009-11-07, Joshua Berry  wrote:
> On Mon, Nov 2, 2009 at 8:35 AM, Jasen Betts  wrote:
>|
>| for this case: convert to seconds and then do abs.
>|
>|  select * from enviados e, recibidos r where abs(extract ( epoch from
>|  (e.fecha - r.fecha) )) < 1
>
> Cheers for that. The query cost is pretty heavy, but the same as the
> following query which involves two WHERE sections:

indeed, this one should be more efficient but is not exactly identical.

select * from enviados e, recibidos r where e.fecha between  r.fecha
-'1s' and r.fecha +'1s';

the difference being that the above includes the case where the
difference is exactly 1 second.

this might work better:

select * from enviados e, recibidos r where e.fecha between  r.fecha 
-'0.99s' and r.fecha +'0.99s'; 

it depends how critical the edge cases are.


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

[GENERAL] dumping parts of a database

[Malm Paul]

Hi List,
I have a database with two tables header and idata, they are connected with an 
conn_ID.
I would like to dump header and the connected idata tables with a certain 
conn_ID. This I would like to import to the same type of database on another 
PostgreSQL server.

I would be gratefull if someone could tell me how I dump those tables and how 
do I import them in another database?

Kind regards,
Paul

Re: [GENERAL] passing parameters to multiple statements

[Pavel Stehule]

Hello

2009/11/16 Konstantin Izmailov :
> I'm planning to use multiple statements via libpq. Before starting coding
> I'm trying to understand are there any limitations on passing parameters.
> E.g. would the following work:
>   PQexecParams(conn, "BEGIN;INSERT INTO tbl VALUES($1,$2);SELECT
> lastval();SELECT * INTO AUDIT FROM (SELECT $3, 'tbl action',
> lastval());COMMIT;", 3, ...);
>

sorry, it is not direct reply on your question, but why don't you  use
simply stored procedure?

btw. It looks like case for trigger
did you know RETURNING clause?

your code is very cumbrous

Regards
Pavel Stehule

> 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

[GENERAL] What is the correct way to extract values from an int8 array in SPI?

[Boszormenyi Zoltan]

Hi,

I am using this code on 8.4/8.5, which works on 64-bit,
but segfaults on 32-bit Linux:

oids[0] = TEXTOID;
values[0] = lex;
nulls[0] = false;
ret = SPI_execute_with_args(
(just_title ?
"SELECT ids FROM product.t_product_inv_titleonly WHERE word
= $1"
:  "SELECT ids FROM product.t_product_inv WHERE word = $1"),
1, oids, values, nulls, true, 1);
if (ret != SPI_OK_SELECT)
elog(ERROR, "SPI_execute_with_args(SELECT FROM
product.t_product_inv[_titleonly]) error (%d)", ret);
prod_inv = SPI_tuptable;

/* If no such record, quit */
if (SPI_processed == 0)
break;

//ids =
PointerGetDatum(PG_DETOAST_DATUM(SPI_getbinval(prod_inv->vals[0],
prod_inv->tupdesc, 1, &isnull)));
ids = SPI_getbinval(prod_inv->vals[0], prod_inv->tupdesc, 1, &isnull);
n_ids = DatumGetInt32(DirectFunctionCall2(array_length, ids,
Int32GetDatum(1)));
ids_arr = DatumGetArrayTypeP(ids);
ids_data = (Datum *) ARR_DATA_PTR(ids_arr);

/* Set up the initial indexes for binary search */
idx_min = 0;
idx_max = n_ids - 1;
idx_mid = (idx_max + idx_min) / 2;

elog(NOTICE, "n_ids %d idx_min %d idx_max %d idx_mid %d", n_ids,
idx_min, idx_max, idx_mid);

elog(NOTICE, "getting display_price of last element %" PRId64,
DatumGetInt64(ids_data[idx_max]));

Field "ids" in the above query is an int8[] array
with SET STORAGE EXTERNAL.
Executing DatumGetInt64(ids_data[idx_max]) in
the last elog() call segfaults in 32-bit. It doesn't matter
if the SPI_getbinval() call above is detoasted or not.
How can I extract individual elements from the int8[]
Datum correctly?

Thanks in advance,
Zoltán Böszörményi

-- 
Bible has answers for everything. Proof:
"But let your communication be, Yea, yea; Nay, nay: for whatsoever is more
than these cometh of evil." (Matthew 5:37) - basics of digital technology.
"May your kingdom come" - superficial description of plate tectonics

--
Zoltán Böszörményi
Cybertec Schönig & Schönig GmbH
http://www.postgresql.at/


-- 
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] -c argument not recognized

[Jasen Betts]

On 2009-11-10, Antonio Ruggiero  wrote:
> I am running PostgresSQL 8.3.8 on windows XP-64Bit.  I am using psql
> client from the PostgreSQL installation and not the Cygwin client.
>
> My issue is that the command line argument "-c command" is not
> recognized. For example, if I run (from Cygwin)
>
> psql -h localhost -p 5432 -d $database postgres -c "select count(*)
> from  $table_name"

On windows you must put the command line arguments in the correct order
(see the documentation for details of correct order)

On linux (and I assume other POSIX) the order is not critical.

windows command-line sucks anyway, so using pipes or temporary files
for the commands is probably a better solution, especially if the
commands are determined at runtime.





-- 
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] "money" binary representation

[Jasen Betts]

On 2009-11-16, Konstantin Izmailov  wrote:
> --000e0cd5d09230ff7d04787526aa
> Content-Type: text/plain; charset=ISO-8859-1
>
> I'm trying to read "money" field using PQgetvalue (PostgreSQL 8.3.7). The
> function returns 9 bytes, smth like 0h 0h 0h 0h 0h 0h 14h 0h 0h, for the
> value '$50.2'. I could not find description anywhere on how to convert the
> binary data into, for example, a double precision number.

money is a 64 bit integer representing a number of cents, it appears
to be big-endian byte order on ypur system

it is deprecated and should not be used in new applications, use some
sort of numeric instead.

> Would you please help me find a method of converting binary "money" data
> into a double precision?

floating point is not recomended for financial calculations, but
dividing the integer value by 100.0 should get you there.


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