How can I return a bigint value from a C extension function?
I have a table with a bytea column:
CREATE TABLE mydata(data bytea);
I would like to select and index based on a subset of the data:
CREATE FUNCTION get_key(bytea) returns BIGINT AS '/lib/data.so'
LANGUAGE C IMMUTABLE;
CREATE INDEX my
We currently evaluate PgFouine to analyse all executed statements in a
Postgres 8.4 cluster. But we don't use syslog - instead we use logging
via STDERR. Analyzing of the logs works fine with that combination. We
also tested big logfiles (~ 3GB) with pgFouine - also works fine, but
took about 2h30m
On Sun, Nov 22, 2009 at 10:41 PM, Craig Ringer
wrote:
> Tom Lane wrote:
> It'd be a HUGE benefit in deployment and update scripts to have PL/PgSQL
> installed and available by default, at least to the superuser and to
> the DB owner.
Are there any known security problems with plpgsql?
--
Sent
Tom Lane wrote:
> Andrew Dunstan writes:
>> Part of the motivation for allowing inline blocks was to allow for
>> conditional logic.
>
> I don't think that argument really applies to this case, because the
> complaint was about not being sure if plpgsql is installed. If it
> isn't, you can hard
On Sun, Nov 22, 2009 at 10:19 PM, Tom Lane wrote:
> Andrew Dunstan writes:
>> Part of the motivation for allowing inline blocks was to allow for
>> conditional logic.
>
> I don't think that argument really applies to this case, because the
> complaint was about not being sure if plpgsql is instal
Andrew Dunstan writes:
> Part of the motivation for allowing inline blocks was to allow for
> conditional logic.
I don't think that argument really applies to this case, because the
complaint was about not being sure if plpgsql is installed. If it
isn't, you can hardly use a plpgsql DO block to
Tom Lane wrote:
[ thinks for awhile... ] Actually, CREATE LANGUAGE is unique among
creation commands in that the common cases have no parameters, at least
not since we added pg_pltemplate. So you could imagine defining CINE
for a language as disallowing any parameters and having these semanti
Dear Sir
Brian Modra wrote:
You can use a plpgsql to do that e.g.
create or replace function getMatchingRecord(vseek text)
returns text as $$
declare
str text;
len integer;
ret text;
...
I Just try your solution , and it's work like a charm
Thankyou for your enlightment
Sincerely
-bino
Robert Haas writes:
> On Sun, Nov 22, 2009 at 6:51 PM, Tom Lane wrote:
>> CREATE IF NOT EXISTS has been proposed and rejected before, more than
>> once. Please see the archives.
> Search for CINE to find the discussions. This is a good place to start:
> http://archives.postgresql.org/pgsql-hac
On Sun, Nov 22, 2009 at 6:51 PM, Tom Lane wrote:
> Craig Ringer writes:
>> I do think this comes up often enough that a built-in trigger "update
>> named column with result of expression on insert" trigger might be
>> desirable.
>
> There's something of the sort in contrib already, I believe, tho
2009/11/23 Bino Oetomo :
> Dear All
>
> Suppose I created a database with single table like this :
> --start--
> CREATE DATABASE bino;
> CREATE TABLE myrecords(record text);
> --end
>
> and I fill myrecords with this :
> --start--
> COPY myrecords (record) FR
Daniel writes:
> PGconn pg_conn;
You probably want that to be "PGconn *pg_conn;"
> db_util.h:11: error: field 'pg_conn' has incomplete type
libpq doesn't expose the struct type PGconn, only pointers to it.
regards, tom lane
--
Sent via pgsql-general mailing list (
Dear All
Suppose I created a database with single table like this :
--start--
CREATE DATABASE bino;
CREATE TABLE myrecords(record text);
--end
and I fill myrecords with this :
--start--
COPY myrecords (record) FROM stdin;
1
12
123
1234
\.
--end
When running a gcc makefile how do I tell the linker about postgres?
I have installed postresql and lsof and I am running all this on
Gentoo Linux.
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-
Here is the code:
#include "libpq-fe.h"
#ifndef HDBUTIL
#define HDBUTIL
class DBConn
{
public:
PGconn pg_conn;
bool Connect();
bool Disconnect();
};
#endif
... and here is the make error:
tux tb # make
g++ -Wall -g -o0 -lpq -I/usr/include/postgresql/libpq-4 server.cpp
d
OK, sorry forget this post I made some progress and now have a slighty
different problem, so i'll start a new post.
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Jonathan Blitz writes:
> What exactly does this message mean?
It means you can't write something like avg(sum(x)).
regards, tom lane
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mai
> MySQL had the following syntax available:
> `updated_date` timestamp NOT NULL default CURRENT_TIMESTAMP on update
> CURRENT_TIMESTAMP
I wonder supporting this syntax would speed things up a little bit.
Here's a simple benchmark about the situation we are discussing here:
There are 2 tables:
Hi,
I am getting the message 'ERROR: aggregate function calls cannot be nested"
when using a select from an inner select.
The outer select had a group by clause but the inner one is a straight join
between a few tables.
What exactly does this message mean?
Jonathan Blitz
Craig Ringer writes:
> I do think this comes up often enough that a built-in trigger "update
> named column with result of expression on insert" trigger might be
> desirable.
There's something of the sort in contrib already, I believe, though
it's so old it still uses abstime :-(
> So might "CRE
2009/11/22 Craig Ringer
> On 23/11/2009 4:15 AM, Scott Marlowe wrote:
> > On Sun, Nov 22, 2009 at 12:50 PM, Thom Brown
> wrote:
> >> Hi,
> >> This should be simple, but for some reason I'm not quite sure what the
> >> solution is. I want to be able to update the value of a column for rows
> >>
On 23/11/2009 4:15 AM, Scott Marlowe wrote:
> On Sun, Nov 22, 2009 at 12:50 PM, Thom Brown wrote:
>> Hi,
>> This should be simple, but for some reason I'm not quite sure what the
>> solution is. I want to be able to update the value of a column for rows
>> that have been updated. More specifical
Thanks to all those who responded to my posting yesterday.
I have now tried a simple simulation of joining tables with partly
overlapping rectangular boxes using Rtrees (with GIST automatically
replacing them in 8.4.1), and this works in 8.1.0 and 8.4.1, with the
latter a bit faster. But my o
On Sun, Nov 22, 2009 at 12:38:36PM -0500, Tom Lane wrote:
> > But when I want to join on this view, it all goes pear shaped, as can
> > be seen in the following (completely equivalent) query:
>
> > # explain select * from maxrevs where rul_id=ANY(select rul_id from rules
> > where rul_sid=15895);
2009/11/22 Christophe Pettus
> David Fetter and Andreas Scherbaum also have solutions for this in
> reployment scripts:
>
>
> http://people.planetpostgresql.org/dfetter/index.php?/archives/23-CREATE-OR-REPLACE-LANGUAGE.html
>
> http://andreas.scherbaum.la/blog/archives/346-create-language-if-not-
David Fetter and Andreas Scherbaum also have solutions for this in
reployment scripts:
http://people.planetpostgresql.org/dfetter/index.php?/archives/23-CREATE-OR-REPLACE-LANGUAGE.html
http://andreas.scherbaum.la/blog/archives/346-create-language-if-not-exist.html
--
-- Christ
On Sunday 22 November 2009 1:10:36 pm Thom Brown wrote:
> 2009/11/22 Scott Marlowe
>
> > > Thanks Scott. It's a shame a function has to be used because it then
> > > has the dependency of plpgsql being loaded. I'm attempting to write a
> >
> > database
> >
> > > schema to accompany a PostgreSQL
2009/11/22 Scott Marlowe
> > Thanks Scott. It's a shame a function has to be used because it then has
> > the dependency of plpgsql being loaded. I'm attempting to write a
> database
> > schema to accompany a PostgreSQL driver for a popular CMS, but I guess I
> > could get it to load plpgsql in
On Sun, Nov 22, 2009 at 1:32 PM, Thom Brown wrote:
> Thanks Scott. It's a shame a function has to be used because it then has
> the dependency of plpgsql being loaded. I'm attempting to write a database
> schema to accompany a PostgreSQL driver for a popular CMS, but I guess I
> could get it to
On Sun, Nov 22, 2009 at 1:32 PM, Thom Brown wrote:
> 2009/11/22 Scott Marlowe
>>
>> On Sun, Nov 22, 2009 at 12:50 PM, Thom Brown wrote:
>> > Hi,
>> > This should be simple, but for some reason I'm not quite sure what the
>> > solution is. I want to be able to update the value of a column for ro
2009/11/22 Scott Marlowe
> On Sun, Nov 22, 2009 at 12:50 PM, Thom Brown wrote:
> > Hi,
> > This should be simple, but for some reason I'm not quite sure what the
> > solution is. I want to be able to update the value of a column for rows
> > that have been updated. More specifically, if a row
On Sunday 22 November 2009 12:09:04 pm Thom Brown wrote:
> 2009/11/22 Aaron Burnett
>
> > this is how I do it if this helps:
> >
> > column_name timestamp without time zone NOT NULL DEFAULT
> > ('now'::text)::timestamp(6) without time zone
> >
> > Hi Aaron. Thanks for the reply, but that would on
On Sun, Nov 22, 2009 at 12:50 PM, Thom Brown wrote:
> Hi,
> This should be simple, but for some reason I'm not quite sure what the
> solution is. I want to be able to update the value of a column for rows
> that have been updated. More specifically, if a row is updated, I want it's
> modified_da
this is how I do it if this helps:
column_name timestamp without time zone NOT NULL DEFAULT
('now'::text)::timestamp(6) without time zone
-Original Message-
From: pgsql-general-ow...@postgresql.org on behalf of Thom Brown
Sent: Sun 11/22/2009 2:50 PM
To: PGSQL Mailing List
Subject: [G
2009/11/22 Aaron Burnett
>
> this is how I do it if this helps:
>
> column_name timestamp without time zone NOT NULL DEFAULT
> ('now'::text)::timestamp(6) without time zone
>
> Hi Aaron. Thanks for the reply, but that would only insert the current
date upon insertion into the table, not when the
Hi,
This should be simple, but for some reason I'm not quite sure what the
solution is. I want to be able to update the value of a column for rows
that have been updated. More specifically, if a row is updated, I want it's
modified_date column to be populated with the current time stamp. I've
l
Martijn van Oosterhout writes:
> I've having a smaller planner problem (server version 8.3.5). I have a
> table with revisions (revs) and I have a view on top of that gives the
> latest revision of each rule. When I request a specific rule, it works
> fine.
> ...
> But when I want to join on this
2009/11/22 Ivano Luberti
> I read books about software products when I need to get introduced to
> the whole picture: architecture, underlying technologies.
> In this way I also understand what to search for when I need detailed
> information or tip and tricks.
>
>
> Well that's the problem i
I read books about software products when I need to get introduced to
the whole picture: architecture, underlying technologies.
In this way I also understand what to search for when I need detailed
information or tip and tricks.
Rikard Bosnjakovic ha scritto:
> On Sat, Nov 21, 2009 at 18:10,
Thanks to all those who responded to my posting yesterday.
I have now tried a simple simulation of joining tables with partly
overlapping rectangular boxes using Rtrees (with GIST automatically
replacing them in 8.4.1), and this works in 8.1.0 and 8.4.1, with the
latter a bit faster. But my o
On Sun, Nov 22, 2009 at 12:24:50PM +, Clive Page wrote:
> On 22/11/2009 12:15, Martijn van Oosterhout wrote:
>
>> Looking forward to your explain output.
>
> Here it is (I wrapped some of the longer lines as might not have
> survived the translation to email):
Ok, very interesting, since thi
In response to Jonathan Blitz :
> I was wondering if there is some indication of how well clustered a table is.
>
> In other words, when a Cluster command is performed then a table would be 100%
> clustered.
> As updates etc are made the table clowly loses its clustering.
> Is there any indicatio
I was wondering if there is some indication of how well clustered a table
is.
In other words, when a Cluster command is performed then a table would be
100% clustered.
As updates etc are made the table clowly loses its clustering.
Is there any indication as to how "bad" it is at any one point?
On 21 Nov 2009, at 23:57, Clive Page wrote:
> The relevant bits of SQL I have been using are:
>
> CREATE TEMPORARY TABLE cat4p AS
> SELECT longid, srcid, ra, dec, poserr,
> BOX(POINT(ra+10.0/(3600*COS(RADIANS(dec))), dec+10.0/3600.0),
> POINT(ra-10.0/(3600*COS(RADIANS(dec))), dec-10.0/360
On 22 Nov 2009, at 13:19, Clive Page wrote:
> On 22/11/2009 12:09, Alban Hertroys wrote:
>> If you expect indexes to work efficiently on temporary tables you should
>> analyse them after filling them to update the planner's statistics on their
>> contents. If you don't you get the default query
On 22/11/2009 12:15, Martijn van Oosterhout wrote:
Looking forward to your explain output.
Here it is (I wrapped some of the longer lines as might not have
survived the translation to email):
Postgres v8.1.0
EXPLAIN SELECT a.longid AS longid, b.longid AS blongid,
gcdist(a.ra, a.d
On 22/11/2009 12:09, Alban Hertroys wrote:
If you expect indexes to work efficiently on temporary tables you should
analyse them after filling them to update the planner's statistics on their
contents. If you don't you get the default query plan that's often not
efficient.
Alban
Thanks - I
On Sun, Nov 22, 2009 at 10:55:21AM +, Clive Page wrote:
>> You are AFAICR the first person to have a problem is this area, but if
>
> I find it extremely hard to believe that. All I am doing is finding
> whether pairs of rectangular boxes overlap or not. That is the most
> trivial use of
On 21 Nov 2009, at 23:57, Clive Page wrote:
> CREATE TEMPORARY TABLE cat4p AS
> SELECT longid, srcid, ra, dec, poserr,
> BOX(POINT(ra+10.0/(3600*COS(RADIANS(dec))), dec+10.0/3600.0),
> POINT(ra-10.0/(3600*COS(RADIANS(dec))), dec-10.0/3600.0)) AS errbox
> FROM cat4;
> CREATE INDEX cat4pi
Hoi,
I've having a smaller planner problem (server version 8.3.5). I have a
table with revisions (revs) and I have a view on top of that gives the
latest revision of each rule. When I request a specific rule, it works
fine.
# explain select * from maxrevs where rul_id=(select rul_id from rules wh
On 22/11/2009 11:52, Thom Brown wrote:
Since this is a performance issue, this should probably have been sent
to the pgsql-performance mailing list. But in any case, the vast
majority of performance issues require an EXPLAIN output, or preferably
with ANALYZE also as there is nothing to help d
On 22 Nov 2009, at 11:55, Clive Page wrote:
> On 22/11/2009 10:44, Martijn van Oosterhout wrote:
>> PostgreSQL is used extensively for geometric queries, see postgis. They
>> abandoned rtree a while back because the GiST rtree support was better,
>
> Maybe the support is better, but the performan
2009/11/22 Clive Page
>
>> Unfortunately it isn't a "few minutes". To re-run in v8.1 I have to
> reload many tables into a different installation using v8.1: some of the
> tables have a few million rows and hundreds of columns. Then I have change
> some scripts to add an EXPLAIN command and log
On 22/11/2009 10:44, Martijn van Oosterhout wrote:
PostgreSQL is used extensively for geometric queries, see postgis. They
abandoned rtree a while back because the GiST rtree support was better,
Maybe the support is better, but the performance is obviously not. And
when there is a difference
On Sun, Nov 22, 2009 at 10:23:29AM +, Clive Page wrote:
> Thanks for your reply. I should have said that I was using v8.1. After
> I posted my question, I retried with
> CREATE INDEX ... USING GIST(errbox box_ops)
> and left it to run overnight. The query using the index, which finds
>
On 22/11/2009 05:40, Tom Lane wrote:
No, because the rtree code is gone entirely. We took it out on the
basis of tests showing that the gist implementation performed as well
or better. I'm not sure why it's not working for you, but if you
can provide a more complete test case, we could look int
56 matches
Mail list logo
