Re: [GENERAL] Support for \u0000?

[Matthew Byrne]

I see.  Thanks for the quick responses!

On Wed, Jul 19, 2017 at 11:32 PM, Tom Lane  wrote:

> Matthew Byrne  writes:
> > Would a more feasible approach be to introduce new types (say, TEXT2 and
> > JSONB2 - or something better-sounding) which are the same as the old ones
> > but add for support \u and UTF 0?  This would isolate nul-containing
> > byte arrays to the implementations of those types and keep backward
> > compatibility by leaving TEXT and JSONB alone.
>
> The problem is not inside those datatypes; either text or jsonb could
> trivially store \0 bytes.  The problem is passing such values through
> APIs that don't support it.  Changing those APIs would affect *all*
> datatypes.
>
> regards, tom lane
>

Re: [GENERAL] Support for \u0000?

[Matthew Byrne]

Thanks for the response Tom.  I understand this would be a mammoth task.

Would a more feasible approach be to introduce new types (say, TEXT2 and
JSONB2 - or something better-sounding) which are the same as the old ones
but add for support \u and UTF 0?  This would isolate nul-containing
byte arrays to the implementations of those types and keep backward
compatibility by leaving TEXT and JSONB alone.

Matt

On Wed, Jul 19, 2017 at 7:30 PM, Tom Lane  wrote:

> Matthew Byrne  writes:
> > Are there any plans to support \u in JSONB and, relatedly, UTF code
> > point 0 in TEXT?
>
> No.  It's basically never going to happen because of the widespread use
> of C strings (nul-terminated strings) inside the backend.  Making \0 a
> legal member of strings would break all those internal APIs, requiring
> touching far more code than anyone would want to do.  It'd likely break
> a great deal of client-side code as well.
>
> regards, tom lane
>

Re: [GENERAL] Support for \u0000?

[Tom Lane]

Matthew Byrne  writes:
> Would a more feasible approach be to introduce new types (say, TEXT2 and
> JSONB2 - or something better-sounding) which are the same as the old ones
> but add for support \u and UTF 0?  This would isolate nul-containing
> byte arrays to the implementations of those types and keep backward
> compatibility by leaving TEXT and JSONB alone.

The problem is not inside those datatypes; either text or jsonb could
trivially store \0 bytes.  The problem is passing such values through
APIs that don't support it.  Changing those APIs would affect *all*
datatypes.

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] Support for \u0000?

[Tom Lane]

Matthew Byrne  writes:
> Are there any plans to support \u in JSONB and, relatedly, UTF code
> point 0 in TEXT?

No.  It's basically never going to happen because of the widespread use
of C strings (nul-terminated strings) inside the backend.  Making \0 a
legal member of strings would break all those internal APIs, requiring
touching far more code than anyone would want to do.  It'd likely break
a great deal of client-side code as well.

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] Support for \u0000?

[Matthew Byrne]

Are there any plans to support \u in JSONB and, relatedly, UTF code
point 0 in TEXT?  To the best of my knowledge \u is valid in JSON and
code point 0 is valid in UTF-8 but Postgres rejects both, which severely
limits its usefulness in many cases.

I am currently working around the issue by using the JSON type, which
allows \u to be stored, but this is far from ideal because it can't be
cast to TEXT or JSONB and can't even be accessed:

mydb=# select '{"thing":"\u"}'::json->>'thing';
ERROR:  unsupported Unicode escape sequence
DETAIL:  \u cannot be converted to text.
CONTEXT:  JSON data, line 1: {"thing":...

Regards,

Matt

Re: [GENERAL] Support for BDR in 9.5?

[Andrew Biggs (adb)]

On 1/8/16, 12:51 PM, "Simon Riggs" 
mailto:si...@2ndquadrant.com>> wrote:

On 8 January 2016 at 18:56, Joshua D. Drake 
mailto:j...@commandprompt.com>> wrote:
On 01/08/2016 10:42 AM, Andrew Biggs (adb) wrote:

Installed 9.5 to CentOS7 via yum, and tried going through the BDR
quick-start guide (minus sections 2.1):

http://bdr-project.org/docs/stable/quickstart.html

It was unhappy that BDR binaries were not on the path, and failed at
section 2.4.

Then went back and tried installing the BDR patch (section 2.1) which
finished with this unhappy message:

 configure: error: bdr is not compatible with postgres 9.5

I was just curious if others have had success with this.

Although many of the components of BDR are in 9.5, BDR itself is still 
currently a fork. It can be downloaded from 2ndQuadrant. In theory it will be 
available in 9.6.

BDR 9.4 is currently at v0.9.3. There isn't a BDR 9.5, since we concentrated on 
pglogical.

pglogical works with 9.4 and 9.5 and is currently at v1.0

Future detailed planning for BDR and pglogical is happening now; there 
definitely will be future versions with increasing PostgreSQL compatibility.

Got it.  Thanks guys, very much appreciated!

Re: [GENERAL] Support for BDR in 9.5?

[Simon Riggs]

On 8 January 2016 at 18:56, Joshua D. Drake  wrote:

> On 01/08/2016 10:42 AM, Andrew Biggs (adb) wrote:
>
> Installed 9.5 to CentOS7 via yum, and tried going through the BDR
>> quick-start guide (minus sections 2.1):
>>
>> http://bdr-project.org/docs/stable/quickstart.html
>>
>> It was unhappy that BDR binaries were not on the path, and failed at
>> section 2.4.
>>
>> Then went back and tried installing the BDR patch (section 2.1) which
>> finished with this unhappy message:
>>
>>  configure: error: bdr is not compatible with postgres 9.5
>>
>> I was just curious if others have had success with this.
>>
>
> Although many of the components of BDR are in 9.5, BDR itself is still
> currently a fork. It can be downloaded from 2ndQuadrant. In theory it will
> be available in 9.6.
>

BDR 9.4 is currently at v0.9.3. There isn't a BDR 9.5, since we
concentrated on pglogical.

pglogical works with 9.4 and 9.5 and is currently at v1.0

Future detailed planning for BDR and pglogical is happening now; there
definitely will be future versions with increasing PostgreSQL compatibility.

-- 
Simon Riggshttp://www.2ndQuadrant.com/

PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

Re: [GENERAL] Support for BDR in 9.5?

[Andreas Kretschmer]

Afaik no, you have to use 9.4.


Am 8. Januar 2016 18:39:07 MEZ, schrieb "Andrew Biggs (adb)" :
>Can anyone tell me if PostgreSQL 9.5 supports (either natively or by
>extension) the BDR functionality?
>
>I tried it out and ran into issues, but it could well have been I was
>doing something wrong.
>
>Thanks!
>Andrew

-- 
Diese Nachricht wurde von meinem Android-Mobiltelefon mit K-9 Mail gesendet.

Re: [GENERAL] Support for BDR in 9.5?

[Roland van Laar]

On January 8, 2016 7:42:06 PM GMT+01:00, "Andrew Biggs (adb)"  
wrote:
>On 1/8/16, 10:53 AM, Rob Sargent wrote:
>
>On 01/08/2016 10:39 AM, Andrew Biggs (adb) wrote:
>Can anyone tell me if PostgreSQL 9.5 supports (either natively or by
>extension) the BDR functionality?
>
>I tried it out and ran into issues, but it could well have been I was
>doing something wrong.
>
>Thanks!
>Andrew
>
>I'm sure those who might be able to help you would be interested in
>what, exactly, it was you tried.
>
>Installed 9.5 to CentOS7 via yum, and tried going through the BDR
>quick-start guide (minus sections 2.1):
>
>http://bdr-project.org/docs/stable/quickstart.html
>
>It was unhappy that BDR binaries were not on the path, and failed at
>section 2.4.
>
>Then went back and tried installing the BDR patch (section 2.1) which
>finished with this unhappy message:
>
>configure: error: bdr is not compatible with postgres 9.5
>
>I was just curious if others have had success with this.

Bdr is only compatible with 9.4 and needs the specific bdr postgres.

Roland
>
>Andrew



-- 
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] Support for BDR in 9.5?

[Joshua D. Drake]

On 01/08/2016 10:42 AM, Andrew Biggs (adb) wrote:


Installed 9.5 to CentOS7 via yum, and tried going through the BDR
quick-start guide (minus sections 2.1):

http://bdr-project.org/docs/stable/quickstart.html

It was unhappy that BDR binaries were not on the path, and failed at
section 2.4.

Then went back and tried installing the BDR patch (section 2.1) which
finished with this unhappy message:

 configure: error: bdr is not compatible with postgres 9.5

I was just curious if others have had success with this.


Although many of the components of BDR are in 9.5, BDR itself is still 
currently a fork. It can be downloaded from 2ndQuadrant. In theory it 
will be available in 9.6.


JD




--
Command Prompt, Inc. - http://www.commandprompt.com/  503-667-4564
PostgreSQL Centered full stack support, consulting and development.
Announcing "I'm offended" is basically telling the world you can't
control your own emotions, so everyone else should do it for you.


--
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] Support for BDR in 9.5?

[Andrew Biggs (adb)]

On 1/8/16, 10:53 AM, Rob Sargent wrote:

On 01/08/2016 10:39 AM, Andrew Biggs (adb) wrote:
Can anyone tell me if PostgreSQL 9.5 supports (either natively or by extension) 
the BDR functionality?

I tried it out and ran into issues, but it could well have been I was doing 
something wrong.

Thanks!
Andrew

I'm sure those who might be able to help you would be interested in what, 
exactly, it was you tried.

Installed 9.5 to CentOS7 via yum, and tried going through the BDR quick-start 
guide (minus sections 2.1):

http://bdr-project.org/docs/stable/quickstart.html

It was unhappy that BDR binaries were not on the path, and failed at section 
2.4.

Then went back and tried installing the BDR patch (section 2.1) which finished 
with this unhappy message:

configure: error: bdr is not compatible with postgres 9.5

I was just curious if others have had success with this.

Andrew

Re: [GENERAL] Support for BDR in 9.5?

[Rob Sargent]

On 01/08/2016 10:39 AM, Andrew Biggs (adb) wrote:
Can anyone tell me if PostgreSQL 9.5 supports (either natively or by 
extension) the BDR functionality?


I tried it out and ran into issues, but it could well have been I was 
doing something wrong.


Thanks!
Andrew


I'm sure those who might be able to help you would be interested in 
what, exactly, it was you tried.

[GENERAL] Support for BDR in 9.5?

[Andrew Biggs (adb)]

Can anyone tell me if PostgreSQL 9.5 supports (either natively or by extension) 
the BDR functionality?

I tried it out and ran into issues, but it could well have been I was doing 
something wrong.

Thanks!
Andrew

Re: [GENERAL] Support for hardware tokens for server/replication private key

[mdaswani]

Thanks for the reply. I can now confirm that replication connections can work
using a private key stored on a hardware token. Do you know if there's any
way I can store the server key on the hardware token?



--
View this message in context: 
http://postgresql.nabble.com/Support-for-hardware-tokens-for-server-replication-private-key-tp5876047p5877762.html
Sent from the PostgreSQL - general mailing list archive at Nabble.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] Support for hardware tokens for server/replication private key

[Magnus Hagander]

On Thu, Dec 3, 2015 at 5:31 AM, mdaswani  wrote:

> Hi,
>
> Postgres allows client-side SSL requests to use secret keys on hardware
> tokens via OpenSSL engine support. Is there an equivalent way to store the
> server key on a hardware token.
>
> Similarly, is it possible to specify private keys on a hardware token for
> replication connections? Does the sslkey parameter of the primary_conninfo
> string in the recovery.conf file accept an OpenSSL Engine token key?
>

While I haven't tested it and haven't heard of anybody else who has, it
should work. From a libpq perspective ,the replication standby is "just
another client", so any parameters that work for libpq should work there.

-- 
 Magnus Hagander
 Me: http://www.hagander.net/
 Work: http://www.redpill-linpro.com/

[GENERAL] Support for hardware tokens for server/replication private key

[mdaswani]

Hi,

Postgres allows client-side SSL requests to use secret keys on hardware
tokens via OpenSSL engine support. Is there an equivalent way to store the
server key on a hardware token.

Similarly, is it possible to specify private keys on a hardware token for
replication connections? Does the sslkey parameter of the primary_conninfo
string in the recovery.conf file accept an OpenSSL Engine token key?

Thanks,
MD.



--
View this message in context: 
http://postgresql.nabble.com/Support-for-hardware-tokens-for-server-replication-private-key-tp5876047.html
Sent from the PostgreSQL - general mailing list archive at Nabble.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] support for ltree

[Tom Lane]

Michael Shapiro  writes:
> Thanks for this response. It still leave my question unanswered. I should
> rephrase it -- will  become a native datatype in Postgres (as
> opposed to remaining an extension). Are there any plans to make  a
> native datatype?

No.  That is not the same as it being unsupported.  Postgres is built
around the concept of being extensible, and one important aspect of that
is having some "standard" extensions as test cases.  ltree seems to fit
that category quite well, in that it's useful for some people but not so
widely used as to need to be in core.

(Even if there were a credible argument for putting ltree in core, I doubt
it would win out over backwards-compatibility concerns.  We've found in
the past that moving things into core is not exactly transparent.)

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] support for ltree

[David G. Johnston]

On Friday, June 12, 2015, Michael Shapiro  wrote:
>
> The reason I am asking is that, although ltree seems to have been a
> contributed module since at least 8.3, how can one know if it will always
> be part of subsequent versions of Postgres?
>

Whether contrib, core, or an external extension you like will always have
ltree available to you.

It is also open source so you could update and compile it yourself in a
worse case scenario.

The difference between core and contrib is the subject of many
discussions but anything there now is almost guaranteed to be in one or the
other indefinitely.

David J.

Re: [GENERAL] support for ltree

[David G. Johnston]

On Friday, June 12, 2015, Michael Shapiro  wrote:

> Hi Melvin,
>
> Thanks for this response. It still leave my question unanswered. I should
> rephrase it -- will  become a native datatype in Postgres (as
> opposed to remaining an extension). Are there any plans to make  a
> native datatype?
>

Instead of asking people to guess about the future why don't you explain
what motivated you to ask the question in the first place?

David J.

Re: [GENERAL] support for ltree

[Michael Shapiro]

Hi Melvin,

Thanks for this response. It still leave my question unanswered. I should
rephrase it -- will  become a native datatype in Postgres (as
opposed to remaining an extension). Are there any plans to make  a
native datatype?

Michael

On Fri, Jun 12, 2015 at 7:58 AM, Melvin Davidson 
wrote:

> Geometric Data Types have been in PostgreSQL for quite a while.
> http://www.postgresql.org/docs/9.2/interactive/datatype-geometric.html
>
> JSON have been in PostgreSQL since 9.2 and it's functionality increases
> with each new version.
> http://www.postgresql.org/docs/9.2/interactive/datatype-json.html
>
> AFAIK, Most of the contributed modules have been changed into EXTENSIONs
> http://www.postgresql.org/docs/9.2/interactive/extend-extensions.html
>
> http://www.postgresql.org/docs/9.2/interactive/external-extensions.html
>
> Check the /share/contrib  and /share/extension  directories under the
> PostgreSQL main directory to see what is available for your version.
>
> On Fri, Jun 12, 2015 at 8:22 AM, Michael Shapiro 
> wrote:
>
>> I am wondering if the contributed module  will always be part of
>> Postgres? Do contributed modules ever get absorbed into Postgres itself?
>>
>> The reason I am asking is that, although ltree seems to have been a
>> contributed module since at least 8.3, how can one know if it will always
>> be part of subsequent versions of Postgres?
>>
>> Are there any plans to make  a built-in datatype, like ,
>> o, or the various Geometric Types (eg, )?
>>
>> Michael Shapiro
>> Senior Systems Engineer
>> National Center for Supercomputing Applications (NCSA)
>> University of Illinois, Urbana-Champaign (UIUC)
>>
>
>
>
> --
> *Melvin Davidson*
> I reserve the right to fantasize.  Whether or not you
> wish to share my fantasy is entirely up to you.
>

Re: [GENERAL] support for ltree

[Melvin Davidson]

Geometric Data Types have been in PostgreSQL for quite a while.
http://www.postgresql.org/docs/9.2/interactive/datatype-geometric.html

JSON have been in PostgreSQL since 9.2 and it's functionality increases
with each new version.
http://www.postgresql.org/docs/9.2/interactive/datatype-json.html

AFAIK, Most of the contributed modules have been changed into EXTENSIONs
http://www.postgresql.org/docs/9.2/interactive/extend-extensions.html

http://www.postgresql.org/docs/9.2/interactive/external-extensions.html

Check the /share/contrib  and /share/extension  directories under the
PostgreSQL main directory to see what is available for your version.

On Fri, Jun 12, 2015 at 8:22 AM, Michael Shapiro 
wrote:

> I am wondering if the contributed module  will always be part of
> Postgres? Do contributed modules ever get absorbed into Postgres itself?
>
> The reason I am asking is that, although ltree seems to have been a
> contributed module since at least 8.3, how can one know if it will always
> be part of subsequent versions of Postgres?
>
> Are there any plans to make  a built-in datatype, like ,
> o, or the various Geometric Types (eg, )?
>
> Michael Shapiro
> Senior Systems Engineer
> National Center for Supercomputing Applications (NCSA)
> University of Illinois, Urbana-Champaign (UIUC)
>



-- 
*Melvin Davidson*
I reserve the right to fantasize.  Whether or not you
wish to share my fantasy is entirely up to you.

[GENERAL] support for ltree

[Michael Shapiro]

I am wondering if the contributed module  will always be part of
Postgres? Do contributed modules ever get absorbed into Postgres itself?

The reason I am asking is that, although ltree seems to have been a
contributed module since at least 8.3, how can one know if it will always
be part of subsequent versions of Postgres?

Are there any plans to make  a built-in datatype, like ,
o, or the various Geometric Types (eg, )?

Michael Shapiro
Senior Systems Engineer
National Center for Supercomputing Applications (NCSA)
University of Illinois, Urbana-Champaign (UIUC)

Re: [GENERAL] Support functions for GiST index on citext

[Chris Hanks]

On Sat, Aug 16, 2014 at 7:02 PM, Chris Hanks
 wrote:
> Thanks, I have considered it, and I'd like to stick with an array for
> my use case if possible. Also, if citext is being advised against, I'd
> like to know about it, since I use it extensively and have never had
> an issue with it. Can anyone shed some light on this?
>
> Thanks!
> Chris
>
> On Sat, Aug 16, 2014 at 6:27 PM, BladeOfLight16
>  wrote:
>> Have you considered normalizing?
>>
>> Here's a SQLFiddle example: http://sqlfiddle.com/#!15/61897/3/0. It uses
>> text instead of citext, but I imagine your results should be similar. Also,
>> I think usage of citext is generally recommended against.
>>
>> The basic idea is to not use an array but use a second table instead. This
>> is well suited to your problem since you need a global unique constraint
>> across all entries; a unique index will be a very efficient way of
>> constraining that. You get your data back into the array form by doing a
>> JOIN and grouping by the first table's primary key.
>>
>>
>> On Fri, Aug 15, 2014 at 11:46 AM, Chris Hanks
>>  wrote:
>>>
>>> Hi -
>>>
>>> I have a table with a citext[] column, and I'm trying to write a
>>> uniqueness constraint for the array values. That is, two rows with
>>> {one,two} and {two,three} would conflict. Since it's citext, also
>>> {one,two} and {TWO, THREE} should conflict too.
>>>
>>> My first thought was to make a unique index using GIN, but that
>>> doesn't seem to be an option. Someone in IRC suggested an exclusion
>>> constraint, but it looks like the citext extension doesn't include any
>>> support for GiST operators.
>>>
>>> So now I'm trying to write my own GiSt-citext operator class to
>>> accomplish this. So far I have:
>>>
>>> CREATE OPERATOR CLASS _citext_ops DEFAULT
>>>   FOR TYPE _citext USING gist AS
>>>   OPERATOR 3 &&(anyarray, anyarray),
>>>   OPERATOR 7 @>(anyarray, anyarray),
>>>   OPERATOR 8 <@(anyarray, anyarray),
>>>   OPERATOR 6 =(anyarray, anyarray),
>>>   FUNCTION 7 citext_eq(citext, citext),
>>>   STORAGE citext;
>>>
>>> I know I need more functions, but I'm not sure what they should be, or
>>> if its even possible to do this in raw SQL (I'm hosted on Heroku so I
>>> don't have the freedom to compile my own functions in C, even if I
>>> knew it).
>>>
>>> Can anyone guide me on how to finish this, or maybe on a simpler way
>>> to accomplish the same thing?
>>>
>>> Thanks!
>>> Chris
>>>
>>>
>>> --
>>> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
>>> To make changes to your subscription:
>>> http://www.postgresql.org/mailpref/pgsql-general
>>
>>

I don't mean to spam the list, but just a final check to see whether
anyone has a solution to this? For my use case, I think that if I
can't get an exclusion constraint working for this I'll probably wind
up just using a GIN index and not worrying about the occasional
duplicate. Though I'm not sure if that's possible either right now.

Thanks!

(Also, my apologies for top-posting earlier - I don't use mailing
lists often and it's easy to forget.)


-- 
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] Support functions for GiST index on citext

[Chris Hanks]

Thanks, I have considered it, and I'd like to stick with an array for
my use case if possible. Also, if citext is being advised against, I'd
like to know about it, since I use it extensively and have never had
an issue with it. Can anyone shed some light on this?

Thanks!
Chris

On Sat, Aug 16, 2014 at 6:27 PM, BladeOfLight16
 wrote:
> Have you considered normalizing?
>
> Here's a SQLFiddle example: http://sqlfiddle.com/#!15/61897/3/0. It uses
> text instead of citext, but I imagine your results should be similar. Also,
> I think usage of citext is generally recommended against.
>
> The basic idea is to not use an array but use a second table instead. This
> is well suited to your problem since you need a global unique constraint
> across all entries; a unique index will be a very efficient way of
> constraining that. You get your data back into the array form by doing a
> JOIN and grouping by the first table's primary key.
>
>
> On Fri, Aug 15, 2014 at 11:46 AM, Chris Hanks
>  wrote:
>>
>> Hi -
>>
>> I have a table with a citext[] column, and I'm trying to write a
>> uniqueness constraint for the array values. That is, two rows with
>> {one,two} and {two,three} would conflict. Since it's citext, also
>> {one,two} and {TWO, THREE} should conflict too.
>>
>> My first thought was to make a unique index using GIN, but that
>> doesn't seem to be an option. Someone in IRC suggested an exclusion
>> constraint, but it looks like the citext extension doesn't include any
>> support for GiST operators.
>>
>> So now I'm trying to write my own GiSt-citext operator class to
>> accomplish this. So far I have:
>>
>> CREATE OPERATOR CLASS _citext_ops DEFAULT
>>   FOR TYPE _citext USING gist AS
>>   OPERATOR 3 &&(anyarray, anyarray),
>>   OPERATOR 7 @>(anyarray, anyarray),
>>   OPERATOR 8 <@(anyarray, anyarray),
>>   OPERATOR 6 =(anyarray, anyarray),
>>   FUNCTION 7 citext_eq(citext, citext),
>>   STORAGE citext;
>>
>> I know I need more functions, but I'm not sure what they should be, or
>> if its even possible to do this in raw SQL (I'm hosted on Heroku so I
>> don't have the freedom to compile my own functions in C, even if I
>> knew it).
>>
>> Can anyone guide me on how to finish this, or maybe on a simpler way
>> to accomplish the same thing?
>>
>> Thanks!
>> Chris
>>
>>
>> --
>> 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] Support functions for GiST index on citext

[BladeOfLight16]

Have you considered normalizing?

Here's a SQLFiddle example: http://sqlfiddle.com/#!15/61897/3/0. It uses
text instead of citext, but I imagine your results should be similar. Also,
I think usage of citext is generally recommended against.

The basic idea is to not use an array but use a second table instead. This
is well suited to your problem since you need a global unique constraint
across all entries; a unique index will be a very efficient way of
constraining that. You get your data back into the array form by doing a
JOIN and grouping by the first table's primary key.


On Fri, Aug 15, 2014 at 11:46 AM, Chris Hanks  wrote:

> Hi -
>
> I have a table with a citext[] column, and I'm trying to write a
> uniqueness constraint for the array values. That is, two rows with
> {one,two} and {two,three} would conflict. Since it's citext, also
> {one,two} and {TWO, THREE} should conflict too.
>
> My first thought was to make a unique index using GIN, but that
> doesn't seem to be an option. Someone in IRC suggested an exclusion
> constraint, but it looks like the citext extension doesn't include any
> support for GiST operators.
>
> So now I'm trying to write my own GiSt-citext operator class to
> accomplish this. So far I have:
>
> CREATE OPERATOR CLASS _citext_ops DEFAULT
>   FOR TYPE _citext USING gist AS
>   OPERATOR 3 &&(anyarray, anyarray),
>   OPERATOR 7 @>(anyarray, anyarray),
>   OPERATOR 8 <@(anyarray, anyarray),
>   OPERATOR 6 =(anyarray, anyarray),
>   FUNCTION 7 citext_eq(citext, citext),
>   STORAGE citext;
>
> I know I need more functions, but I'm not sure what they should be, or
> if its even possible to do this in raw SQL (I'm hosted on Heroku so I
> don't have the freedom to compile my own functions in C, even if I
> knew it).
>
> Can anyone guide me on how to finish this, or maybe on a simpler way
> to accomplish the same thing?
>
> Thanks!
> Chris
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>

[GENERAL] Support functions for GiST index on citext

[Chris Hanks]

Hi -

I have a table with a citext[] column, and I'm trying to write a
uniqueness constraint for the array values. That is, two rows with
{one,two} and {two,three} would conflict. Since it's citext, also
{one,two} and {TWO, THREE} should conflict too.

My first thought was to make a unique index using GIN, but that
doesn't seem to be an option. Someone in IRC suggested an exclusion
constraint, but it looks like the citext extension doesn't include any
support for GiST operators.

So now I'm trying to write my own GiSt-citext operator class to
accomplish this. So far I have:

CREATE OPERATOR CLASS _citext_ops DEFAULT
  FOR TYPE _citext USING gist AS
  OPERATOR 3 &&(anyarray, anyarray),
  OPERATOR 7 @>(anyarray, anyarray),
  OPERATOR 8 <@(anyarray, anyarray),
  OPERATOR 6 =(anyarray, anyarray),
  FUNCTION 7 citext_eq(citext, citext),
  STORAGE citext;

I know I need more functions, but I'm not sure what they should be, or
if its even possible to do this in raw SQL (I'm hosted on Heroku so I
don't have the freedom to compile my own functions in C, even if I
knew it).

Can anyone guide me on how to finish this, or maybe on a simpler way
to accomplish the same thing?

Thanks!
Chris


-- 
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] Support for Alert

[salah jubeh]

Have a look here 

http://bucardo.org/wiki/Check_postgres
Regards




On Wednesday, February 19, 2014 5:51 PM, Alejandro Carrillo 
 wrote:
 
Hi,

PostgreSQL have a way to put alerts about number of connections, tablespace 
used, etc like the DBMS_SERVER_ALERT package of Oracle?

Thanks you

Re: [GENERAL] Support for Alert

[Joshua D. Drake]

On 02/19/2014 08:49 AM, Alejandro Carrillo wrote:

Hi,

PostgreSQL have a way to put alerts about number of connections,
tablespace used, etc like the DBMS_SERVER_ALERT package of Oracle?

Thanks you


No but any number of monitoring systems already support PostgreSQL:

Zabbix, New Relic, OpenNMS, Nagios

--
Command Prompt, Inc. - http://www.commandprompt.com/  509-416-6579
PostgreSQL Support, Training, Professional Services and Development
High Availability, Oracle Conversion, Postgres-XC, @cmdpromptinc
For my dreams of your image that blossoms
   a rose in the deeps of my heart. - W.B. Yeats


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

[GENERAL] Support for Alert

[Alejandro Carrillo]

Hi,

PostgreSQL have a way to put alerts about number of connections, tablespace 
used, etc like the DBMS_SERVER_ALERT package of Oracle?

Thanks you

Re: [GENERAL] Support for Foreign keys with arrays

[Michael Paquier]

On Wed, Jul 10, 2013 at 3:10 PM, itishree sukla
 wrote:
> Thanks, not sure how to download and apply this patch, not getting any down
> load link. ?
In the section "Comments", some of the lines are referred by "patch".
Click on the latest one. You will be redirected to the email where the
patch is included and will be able to download it directly from there.
--
Michael


-- 
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] Support for Foreign keys with arrays

[itishree sukla]

Thanks, not sure how to download and apply this patch, not getting any down
load link. ?


On Wed, Jul 10, 2013 at 9:16 AM, Michael Paquier
wrote:

> On Tue, Jul 9, 2013 at 6:26 PM, itishree sukla 
> wrote:
> > Hello Every one,
> >
> > I have a requirement for support for foreign keys with arrays, which is
> not
> > there in postgresql 9.2, however it is in development for 9.3, i can see
> > there is some thread saying patch is available, can any one please help
> me
> > to get the patch, or any other work around by which we can achieve this.
> The patch is available in a previous commit fest:
> https://commitfest.postgresql.org/action/patch_view?id=900
> The discussion finished about concerns with performance, and did not go
> further.
> --
> Michael
>

Re: [GENERAL] Support for Foreign keys with arrays

[Michael Paquier]

On Tue, Jul 9, 2013 at 6:26 PM, itishree sukla  wrote:
> Hello Every one,
>
> I have a requirement for support for foreign keys with arrays, which is not
> there in postgresql 9.2, however it is in development for 9.3, i can see
> there is some thread saying patch is available, can any one please help me
> to get the patch, or any other work around by which we can achieve this.
The patch is available in a previous commit fest:
https://commitfest.postgresql.org/action/patch_view?id=900
The discussion finished about concerns with performance, and did not go further.
--
Michael


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

[GENERAL] Support for Foreign keys with arrays

[itishree sukla]

Hello Every one,

I have a requirement for support for foreign keys with arrays, which is not
there in postgresql 9.2, however it is in development for 9.3, i can see
there is some thread saying patch is available, can any one please help me
to get the patch, or any other work around by which we can achieve this.


Regards,
Itishree

[GENERAL] Support of multibyte encoding for pg_trgm

[Brian Hirt]

Teodor,

I ran across a commit message that shows multibyte encoding support in  
8.4 and my testing shows that to be the case as well.   Is there a  
back patch for 8.2?   My own quick attempt at creating one didn't work  
so well and before I start spending some major time trying I thought  
I'd check in with you.


Regards,

Brian Hirt

Postgres Version 8.2 and 8.3
test=# select show_trgm('魔法門英雄無敵2：王位爭奪戰');
   show_trgm
---
 {"  2"," 2 "}
(1 row)

Postgres Version 8.4
test=# select show_trgm('魔法門英雄無敵2：王位爭奪戰');
 
show_trgm

--
 {0x84af82,0x8426fb,0x886567,0x8986ec, 
0x8c3de8,0x9c19f5,0xa0ef88,0xae352c,0xc7de23,0xf449ca, 
0x003dc9,0x1003c8,0x143838,0x64a38f,0x650b2a}

(1 row)


Log Message:
---
Support of multibyte encoding for pg_trgm

Modified Files:
--
pgsql/contrib/pg_trgm:
trgm.h (r1.9 -> r1.10)
(http://anoncvs.postgresql.org/cvsweb.cgi/pgsql/contrib/pg_trgm/trgm.h?r1=1.9&r2=1.10 
)

trgm_gin.c (r1.5 -> r1.6)
(http://anoncvs.postgresql.org/cvsweb.cgi/pgsql/contrib/pg_trgm/trgm_gin.c?r1=1.5&r2=1.6 
)

trgm_op.c (r1.10 -> r1.11)
(http://anoncvs.postgresql.org/cvsweb.cgi/pgsql/contrib/pg_trgm/trgm_op.c?r1=1.10&r2=1.11 
)



--
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] support for embedded db and a clustered index?

[Paul Bohme]

Scott Marlowe wrote:



Also, can pgsql be embedded in an application?  By that I mean it is easily
deployable with an application.



Not really the same thing.  PostgreSQL is not embeddable in the
classic sense, and if you need an embedded database, look at SQLLite.

  

...

I do NOT mean
a db that runs on an embedded device (although I am hoping pgsql has a small
memory footprint) and I also do NOT mean to ask if pgsql supports embedded
sql.



PostgreSQL is not optimized for small memory or disk footprint, it is
optimized for using more memory and being fast and reliable.  I think
you might want SQLLite for this job.
  


I would second the recommendation for SQLite.  Am using it extensively 
in an embedded application with reasonably low constraints (32M of RAM 
and flash) and have nothing but good to say about it.  (I find that its 
C API kicks the tar out of Postgres' API, for usability/friendliness 
btw. ;-)


Different tools for different jobs..

 -P

--
Paul Bohme | Chief Technologist
Brivo Systems LLC | 4330 East-West Highway Suite 250 Bethesda, MD 20814
Office: 301.664.5249 | Fax: 301.664.5264 | [EMAIL PROTECTED]
Brivo's Official Website is: www.brivo.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] support for embedded db and a clustered index?

[Scott Marlowe]

On Sat, Oct 25, 2008 at 12:02 PM, Julian Bui <[EMAIL PROTECTED]> wrote:
> Hi all,
>
> Does pgsql provide support for a clustered index?  By that I mean can I
> specify that the database insert records in the order of a particular
> attribute.  Many of my queries are time-range based and my row inserts are
> done chronologically, so it would benefit to have them sorted by when they
> were inserted.

Sort of.  It supports static clustering.  Updates aren't clustered.

> Also, can pgsql be embedded in an application?  By that I mean it is easily
> deployable with an application.

Not really the same thing.  PostgreSQL is not embeddable in the
classic sense, and if you need an embedded database, look at SQLLite.

>  The end user is unaware that a database
> even exists and the end user does not need to install pgsql.

Yes, that's called a silent install.

> I do NOT mean
> a db that runs on an embedded device (although I am hoping pgsql has a small
> memory footprint) and I also do NOT mean to ask if pgsql supports embedded
> sql.

PostgreSQL is not optimized for small memory or disk footprint, it is
optimized for using more memory and being fast and reliable.  I think
you might want SQLLite for this job.

-- 
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] support for embedded db and a clustered index?

[Julian Bui]

Hi all,

Does pgsql provide support for a clustered index?  By that I mean can I
specify that the database insert records in the order of a particular
attribute.  Many of my queries are time-range based and my row inserts are
done chronologically, so it would benefit to have them sorted by when they
were inserted.

Also, can pgsql be embedded in an application?  By that I mean it is easily
deployable with an application.  The end user is unaware that a database
even exists and the end user does not need to install pgsql.  I do NOT mean
a db that runs on an embedded device (although I am hoping pgsql has a small
memory footprint) and I also do NOT mean to ask if pgsql supports embedded
sql.

Any help would be greatly appreciated.

Thanks,
Julian

Re: [GENERAL] Support for distributed transactions in 8.2.5

[Robert Treat]

On Thursday 11 October 2007 11:50, Richard Huxton wrote:
> Brian Oki (boki) wrote:
> > My question is this: How does PostgreSQL 8.2.5 execute DML statements
> > (insert, update, delete, select) on remote nodes as part of the same
> > transaction? Where is the syntax specified? Or, is there a different
> > model supported? It's sort of like the synchronous multi-master
> > replication mentioned in Chapter 24. I'm looking for an integrated
> > solution.
>
> It doesn't, there isn't, and no.
>

I am thinking that the OP might be thinking about pl/proxy, which you 
interface at the sql level like other procedures, and it does some magic 
behind the scenes to farm queries where they are supposed to go. Granted this 
doesn't really support acid safe distributed dml, but might be what he was 
thinking? 

-- 
Robert Treat
Build A Brighter LAMP :: Linux Apache {middleware} PostgreSQL

---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster

Re: [GENERAL] Support for distributed transactions in 8.2.5

[Richard Huxton]

Brian Oki (boki) wrote:

My question is this: How does PostgreSQL 8.2.5 execute DML statements
(insert, update, delete, select) on remote nodes as part of the same
transaction? Where is the syntax specified? Or, is there a different
model supported? It's sort of like the synchronous multi-master
replication mentioned in Chapter 24. I'm looking for an integrated
solution. 


It doesn't, there isn't, and no.

I'm afraid the 2-phase commit in 8.2 is there as a building-block for 
this sort of thing, rather than a complete solution in itself.


I believe there are some Java tools that might help you here.

--
  Richard Huxton
  Archonet Ltd

---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
  choose an index scan if your joining column's datatypes do not
  match

[GENERAL] Support for distributed transactions in 8.2.5

[Brian Oki (boki)]

Hi,
 
I've read through the relevant documentation on distributed transactions
for PostgreSQL 8.2.5 but it leaves me with more questions than answers.
It is unclear to me how SQL statements can be executed at remote nodes
from a single coordinator and then use distributed two-phase commit (via
'prepare transaction tid' and 'commit prepared'). I worked at Oracle in
the distributed database group and could do things like the following
using PL/SQL, where we insert the same row into the same table on three
different nodes, including the local one:
insert into foo 
insert into [EMAIL PROTECTED] mailto:[EMAIL PROTECTED]>

insert into [EMAIL PROTECTED]   
COMMIT
This sequence will insert a row into all three tables and do the
distributed atomic commitment. 
 
My question is this: How does PostgreSQL 8.2.5 execute DML statements
(insert, update, delete, select) on remote nodes as part of the same
transaction? Where is the syntax specified? Or, is there a different
model supported? It's sort of like the synchronous multi-master
replication mentioned in Chapter 24. I'm looking for an integrated
solution. 
 
Brian Oki, Ph.D.
Cisco Systems, Inc. 

Re: [GENERAL] Support for idempotent schema changes?

[Florian G. Pflug]

Joshua D. Drake wrote:

David Lowe wrote:

Within the context of a script, executing:

Begin
Statement1
Statement2
Statement3
Commit

Where I only wish to commit if the error is specific to the object
already existing, and rollback for all other errors, what's the best way
to accomplish that?
  
You would have to put each statement into a savepoint, and catch each 
error that occured and commit or rollback to a savepoint

based on that result.


You could write a plpgsql function that executes a text given to it
as a parameter, and catches only "already exists" errors. Then your
schema script could look like
select execute_ignoreexists('create table ...') ;
select execute_ignoreexists('create index ...') ;
...

greetings, Florian Pflug

---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster

Re: [GENERAL] Support for idempotent schema changes?

[Joshua D. Drake]

David Lowe wrote:

Within the context of a script, executing:

Begin
Statement1
Statement2
Statement3
Commit

Where I only wish to commit if the error is specific to the object
already existing, and rollback for all other errors, what's the best way
to accomplish that?
  
You would have to put each statement into a savepoint, and catch each 
error that occured and commit or rollback to a savepoint

based on that result.

Joshua D. Drake





-Original Message-
From: Peter Eisentraut [mailto:[EMAIL PROTECTED] 
Sent: Saturday, March 03, 2007 11:45 PM

To: pgsql-general@postgresql.org
Cc: David Lowe
Subject: Re: [GENERAL] Support for idempotent schema changes?

David Lowe wrote:
  

So how can I make statements of the form:



  

*  alter table only customers add constraint
a_previously_missed_constraint unique (a, b, c);

*  add column points int4 not null default 0;



  

idempotent?



You just ignore the error if the object already exists.

  



---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

  http://www.postgresql.org/docs/faq

Re: [GENERAL] Support for idempotent schema changes?

[David Lowe]

Within the context of a script, executing:

Begin
Statement1
Statement2
Statement3
Commit

Where I only wish to commit if the error is specific to the object
already existing, and rollback for all other errors, what's the best way
to accomplish that?


-Original Message-
From: Peter Eisentraut [mailto:[EMAIL PROTECTED] 
Sent: Saturday, March 03, 2007 11:45 PM
To: pgsql-general@postgresql.org
Cc: David Lowe
Subject: Re: [GENERAL] Support for idempotent schema changes?

David Lowe wrote:
> So how can I make statements of the form:

> *  alter table only customers add constraint
> a_previously_missed_constraint unique (a, b, c);
>
> *  add column points int4 not null default 0;

> idempotent?

You just ignore the error if the object already exists.

-- 
Peter Eisentraut
http://developer.postgresql.org/~petere/

---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster

Re: [GENERAL] Support for idempotent schema changes?

[Flemming Frandsen]

Peter Eisentraut wrote:

You just ignore the error if the object already exists.


I'd advice against that or at least make sure that only "the thing 
already exists" errors get ignored.


Because otherwise it's 100% impossible to discover any real problems 
with the scripts.


--
 Regards Flemming Frandsen - YAPH - http://dion.swamp.dk

---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings

Re: [GENERAL] Support for idempotent schema changes?

[Peter Eisentraut]

David Lowe wrote:
> So how can I make statements of the form:

> *  alter table only customers add constraint
> a_previously_missed_constraint unique (a, b, c);
>
> *  add column points int4 not null default 0;

> idempotent?

You just ignore the error if the object already exists.

-- 
Peter Eisentraut
http://developer.postgresql.org/~petere/

---(end of broadcast)---
TIP 6: explain analyze is your friend

[GENERAL] Support for idempotent schema changes?

[David Lowe]

At our development shop we use many different PostgreSQL databases
simultaneously, each corresponding to a specific version of our
software.  For example, a developer might be working on v1.0 and v1.1 at
the same time, while QA is busily testing/verifying version 1.0.3.   All
application code and SQL is managed in SVN, and we use scripts to
automatically update our sandboxes from one version to another.  

 

Each database schema maintains its version history and current version,
and the update scripts can move a schema from one version to the next
(all in the context of a transaction of course).  In general this works
well when updates are applied in order and corresponding to a specific
development branch.   It doesn't work as well when updates need to be
applied out of order corresponding to a different code branch.

 

As I see it the key to making the update process work smoothly is to
make the updates themselves idempotent.   For data oriented operations
this is usually accomplished via 'where not exists' clauses.   However,
most of the updates are not data operations, but schema operations, for
example, adding a missed unique constraint, or a column.   So how can I
make statements of the form:

 

*  alter table only customers add constraint
a_previously_missed_constraint unique (a, b, c);

*  add column points int4 not null default 0;

 

idempotent?

 

I can always fallback on scripting to accomplish this - I can write
functions to check for the 'thing' (column, constraint, whatever) before
attempting to create it (via php, python pgdb, or via stored procedures,
etc.).   But am I missing something in PostgreSQL which would allow me
to accomplish the same in a more direct manner?

 

 

 

Re: [GENERAL] Support for Limit in Update, Insert...

[Jim C. Nasby]

On Fri, Sep 09, 2005 at 06:42:10PM -0500, Jim C. Nasby wrote:
> On Thu, Sep 08, 2005 at 10:49:25PM -0400, Tom Lane wrote:
> > Alvaro Herrera <[EMAIL PROTECTED]> writes:
> > > This has been discussed before, and rejected.  Please see the archives.
> > 
> > For SELECT, both LIMIT and OFFSET are only well-defined in the presence
> > of an ORDER BY clause.  (One could argue that we should reject them when
> > no ORDER BY, but given that the database isn't getting changed as a side
> > effect, that's probably too anal-retentive.  When the database *is*
> > going to be changed, however, I for one like well-defined results.)
> > 
> > If this proposal included adding an ORDER BY to UPDATE/DELETE, then it
> > would at least be logically consistent.  I have not seen the use-case
> > for it though.  In any case you can usually get the equivalent result
> > with something like
> > 
> > UPDATE foo SET ...
> > WHERE pkey IN (SELECT pkey FROM foo ORDER BY ... LIMIT ...);
> 
> BTW, this is a case where using ctid would make sense, though you can't:
> 
> decibel=# update rrs set parent=parent+1 where ctid in (select ctid from
> rrs order by rrs_id limit 1);
> ERROR:  could not identify an ordering operator for type tid
> HINT:  Use an explicit ordering operator or modify the query.
> ERROR:  could not identify an ordering operator for type tid
> HINT:  Use an explicit ordering operator or modify the query.
> decibel=# 

Actually, after trying this, curiosity took hold:
(Note that it's not actually safe to use ctid like this)

decibel=# explain analyze select * from rrs where ctid='(0,3)';
  QUERY PLAN
  
--
 Tid Scan on rrs  (cost=0.00..4.01 rows=1 width=66) (actual time=0.072..0.076 
rows=1 loops=1)
   Filter: (ctid = '(0,3)'::tid)
 Total runtime: 0.265 ms
(3 rows)

decibel=# 

Shouldn't there be an access method that goes directly to the specified
ctid instead of doing a seqscan? Even on a small table it seems this
would be faster than a seqscan.
-- 
Jim C. Nasby, Sr. Engineering Consultant  [EMAIL PROTECTED]
Pervasive Software  http://pervasive.comwork: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf   cell: 512-569-9461

---(end of broadcast)---
TIP 6: explain analyze is your friend

Re: [GENERAL] Support for Limit in Update, Insert...

[Jim C. Nasby]

On Thu, Sep 08, 2005 at 10:49:25PM -0400, Tom Lane wrote:
> Alvaro Herrera <[EMAIL PROTECTED]> writes:
> > This has been discussed before, and rejected.  Please see the archives.
> 
> For SELECT, both LIMIT and OFFSET are only well-defined in the presence
> of an ORDER BY clause.  (One could argue that we should reject them when
> no ORDER BY, but given that the database isn't getting changed as a side
> effect, that's probably too anal-retentive.  When the database *is*
> going to be changed, however, I for one like well-defined results.)
> 
> If this proposal included adding an ORDER BY to UPDATE/DELETE, then it
> would at least be logically consistent.  I have not seen the use-case
> for it though.  In any case you can usually get the equivalent result
> with something like
> 
>   UPDATE foo SET ...
>   WHERE pkey IN (SELECT pkey FROM foo ORDER BY ... LIMIT ...);

BTW, this is a case where using ctid would make sense, though you can't:

decibel=# update rrs set parent=parent+1 where ctid in (select ctid from
rrs order by rrs_id limit 1);
ERROR:  could not identify an ordering operator for type tid
HINT:  Use an explicit ordering operator or modify the query.
ERROR:  could not identify an ordering operator for type tid
HINT:  Use an explicit ordering operator or modify the query.
decibel=# 

-- 
Jim C. Nasby, Sr. Engineering Consultant  [EMAIL PROTECTED]
Pervasive Software  http://pervasive.comwork: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf   cell: 512-569-9461

---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
   choose an index scan if your joining column's datatypes do not
   match

Re: [GENERAL] Support for Limit in Update, Insert...

[Csaba Nagy]

Well, I do have a use case for it. 

Context:

We have data coming in from web requests, which must be fast, so we just
insert them in temporary tables without any verification. Then they are
periodically processed by a background task, but even that one will
process just a chunk at a time to avoid long running queries and the
possible socket timeouts bundled with them. Now for identifying a chunk
we use a "chunkid" field in those temporary tables, which is initially
null. When a chunk is selected for processing, we update the chunkid
field with the next value of a sequence, and then all further processing
has a where clause which selects only records with that chunkid.

Use case:

To set the chunkid only for 1000 rows, we actually don't care which
ones. The idea is to uniquely mark a chunk of data, we really don't care
which rows are selected in each chunk, they will be processed all
eventually.

Of course right now we do it by something similar with what you
proposed, using a subselect with a limit clause, I wonder if a simple
update with limit could be faster ?

Cheers,
Csaba.

On Fri, 2005-09-09 at 04:49, Tom Lane wrote:
> Alvaro Herrera <[EMAIL PROTECTED]> writes:
> > This has been discussed before, and rejected.  Please see the archives.
> 
> For SELECT, both LIMIT and OFFSET are only well-defined in the presence
> of an ORDER BY clause.  (One could argue that we should reject them when
> no ORDER BY, but given that the database isn't getting changed as a side
> effect, that's probably too anal-retentive.  When the database *is*
> going to be changed, however, I for one like well-defined results.)
> 
> If this proposal included adding an ORDER BY to UPDATE/DELETE, then it
> would at least be logically consistent.  I have not seen the use-case
> for it though.  In any case you can usually get the equivalent result
> with something like
> 
>   UPDATE foo SET ...
>   WHERE pkey IN (SELECT pkey FROM foo ORDER BY ... LIMIT ...);
> 
>   regards, tom lane
> 
> ---(end of broadcast)---
> TIP 2: Don't 'kill -9' the postmaster


---(end of broadcast)---
TIP 4: Have you searched our list archives?

   http://archives.postgresql.org

Re: [GENERAL] Support for Limit in Update, Insert...

[Tom Lane]

Alvaro Herrera <[EMAIL PROTECTED]> writes:
> This has been discussed before, and rejected.  Please see the archives.

For SELECT, both LIMIT and OFFSET are only well-defined in the presence
of an ORDER BY clause.  (One could argue that we should reject them when
no ORDER BY, but given that the database isn't getting changed as a side
effect, that's probably too anal-retentive.  When the database *is*
going to be changed, however, I for one like well-defined results.)

If this proposal included adding an ORDER BY to UPDATE/DELETE, then it
would at least be logically consistent.  I have not seen the use-case
for it though.  In any case you can usually get the equivalent result
with something like

UPDATE foo SET ...
WHERE pkey IN (SELECT pkey FROM foo ORDER BY ... LIMIT ...);

regards, tom lane

---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster

Re: [GENERAL] Support for Limit in Update, Insert...

[Alvaro Herrera]

On Thu, Sep 08, 2005 at 07:19:34PM -0600, Cristian Prieto wrote:
> Maybe the first 1000 rows based in the primary index

No, this is not a satisfactory answer, because 1. it's possible that
there's no primary key at all, or 2. said index may not get used for the
execution of the update.  Maybe something else I'm forgetting right now.

This has been discussed before, and rejected.  Please see the archives.

-- 
Alvaro Herrera -- Valdivia, Chile Architect, www.EnterpriseDB.com
"Java is clearly an example of a money oriented programming"  (A. Stepanov)

---(end of broadcast)---
TIP 4: Have you searched our list archives?

   http://archives.postgresql.org

Re: [GENERAL] Support for Limit in Update, Insert...

[Cristian Prieto]

Maybe the first 1000 rows based in the primary index

- Original Message - 
From: "Tom Lane" <[EMAIL PROTECTED]>

To: "Cristian Prieto" <[EMAIL PROTECTED]>
Cc: 
Sent: Thursday, September 08, 2005 6:05 PM
Subject: Re: [GENERAL] Support for Limit in Update, Insert... 




"Cristian Prieto" <[EMAIL PROTECTED]> writes:

Would be any future support for limit in update/insert queries? so you =
could do something like
update table1 set col1=3Dvalue1 limit 1000;
would update just the first 1000 rows in the table.


That seems like a spectacularly bad idea, considering that you could
have no guarantees about *which* 1000 rows get updated.

regards, tom lane

---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
  choose an index scan if your joining column's datatypes do not
  match


---(end of broadcast)---
TIP 6: explain analyze is your friend

Re: [GENERAL] Support for Limit in Update, Insert...

[Tom Lane]

"Cristian Prieto" <[EMAIL PROTECTED]> writes:
> Would be any future support for limit in update/insert queries? so you =
> could do something like
> update table1 set col1=3Dvalue1 limit 1000;
> would update just the first 1000 rows in the table.

That seems like a spectacularly bad idea, considering that you could
have no guarantees about *which* 1000 rows get updated.

regards, tom lane

---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
   choose an index scan if your joining column's datatypes do not
   match

[GENERAL] Support for Limit in Update, Insert...

[Cristian Prieto]

Would be any future support for limit in 
update/insert queries? so you could do something like
 
update table1 set col1=value1 limit 
1000;
 
would update just the first 1000 rows in the table. 
I've been playing a little with the SPI and I get the SPI already has the 
support for limit the number of changes or elements in a query (the SPI_execute 
function).
 
Any comments?
 
Thanks a lot

Re: [GENERAL] support

[Weiping]

金更达 wrote:

>hi,pgsql-genera
>
>I am chinese user, I have installed thd PostGreSQL 8.0 for win in my 
> computer, it's very good.
>but I find a problem, when I use "select * from dcvalue where 
>text_value='中文'" to search record, 
>the system return "no results", 
>
seems like your locale setting doesn't match to your database encoding.
you should use EUC_CN or unicode as your database encoding and zh_CN.utf8
as your locale setting.

regards

laser

---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
  subscribe-nomail command to [EMAIL PROTECTED] so that your
  message can get through to the mailing list cleanly

[GENERAL] support

[金更达]

hi,pgsql-genera

I am chinese user, I have installed thd PostGreSQL 8.0 for win in my 
computer, it's very good.
but I find a problem, when I use "select * from dcvalue where 
text_value='中文'" to search record, 
the system return "no results", moreover when I use "select * from dcvalue 
where text_value like '中文'" 
to search record, the system return "1 record". I have checked the text_value, 
it's length equal 4.
can you tell me why and how to do.
thanks very much.



致
礼！


金更达
[EMAIL PROTECTED]
　　2004-11-20

---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])

[GENERAL] support on postgres

[Vincent . Desloges]

Hello,

We develop SMS application using a proprietary framework installed on Linux
server. This framework installs and creates Postgres 7.1 data base under
/var/lib partition.
Client notices that /var/lib partition seems too small for SMS application
activity. Client would like to move postgres data files from /var/lib to a
larger one.
How can we manage ?

best regards,

subscribe
end



---(end of broadcast)---
TIP 8: explain analyze is your friend

Re: [GENERAL] Support.

[Al Hulaton]

Louis P. Boudville wrote:

1.Where can I get end user support for PostgreSQL ?

You could use this mailing list, the #postgresql channel on the Freenode 
IRC server or do a search for commercial Postgres support. The techdocs 
on the Postgresql.org site would be my first stop, and you can also 
search those techdocs and the O'Reilly 'Practical Postgresql' book on 
our site below.

2.What are the fees like ?

You can get support contracts, pay as you go, by the hour, or free free free on this and other mailing lists. We offer both contracts and pay-as-you-go support because different sized companies need different levels of support. I think you'll find many postgres consultants will offer the same.

--
Best,
Al Hulaton|  Sr. Account Engineer  |  Command Prompt, Inc.
503.667.4564  |  [EMAIL PROTECTED]
Home of Mammoth Replicator for PostgreSQL
Managed PostgreSQL, Linux services and consulting
Read and Search O'Reilly's 'Practical PostgreSQL' at
http://www.commandprompt.com
---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
 subscribe-nomail command to [EMAIL PROTECTED] so that your
 message can get through to the mailing list cleanly

Re: [GENERAL] Support for functions returning mutliple result sets?

[Tom Lane]

<[EMAIL PROTECTED]> writes:
> Does postgres support returning multiple sets from a function?

Not directly.  You can fake it in some cases by returning several open
cursors and expecting the caller to fetch from those cursors.  However,
if you can't write SQL queries that can be executed to return each of
the needed return sets, you're out of luck --- there's nothing so
flexible as RETURN NEXT.

I don't think this is an unfixable limitation; the needed mechanisms
all exist, it's a matter of figuring out what a reasonable syntactic
representation would look like.  Any thoughts?

regards, tom lane

---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])

Re: [GENERAL] Support for functions returning mutliple result sets?

[Joe Conway]

[EMAIL PROTECTED] wrote:
I know I can write plpgsql functions that return sets.
Does postgres support returning multiple sets from a function?
No.

Joe

---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
 subscribe-nomail command to [EMAIL PROTECTED] so that your
 message can get through to the mailing list cleanly

[GENERAL] Support for functions returning mutliple result sets?

[bill.postgresql-users]

I know I can write plpgsql functions that return sets.
Does postgres support returning multiple sets from a function?

Bill McMilleon 


---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
  subscribe-nomail command to [EMAIL PROTECTED] so that your
  message can get through to the mailing list cleanly

[GENERAL] support

[Jason Tesser]

I am looking at PostgreSQL as a possible option for our backend database.  I am also 
evaluating Oracle.  What kind of paid
support does PostgreSQL offer?

Jason Tesser
Web/Multimedia Programmer
Northland Ministries Inc.
(715)324-6900 x3050


---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster

Re: [GENERAL] Support of type procedure

[Peter Eisentraut]

Mourad EL HADJ MIMOUNE writes:

> I have read in some document about Postgres that this last supports
> attrubutes of type procedure.
> Type procedure allows values of an attribute to be represented by a
> procedure.

This presumably existed in or near Berkeley POSTGRES 4.2, and some support
code is still there, but it probably doesn't work anymore, nor is there a
syntax that allows you to create such a thing.

-- 
Peter Eisentraut  [EMAIL PROTECTED]   http://yi.org/peter-e/


---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/users-lounge/docs/faq.html

[GENERAL] Support for Geometric Types

[Paul Ramsey]

I have a couple questions regarding the maturity of the support for
geometric types:

- is there support for indexing geometric types so that things like '='
operators and some of the other geometric operators are a bit faster?
- are there any plans on extending some of the geometric operators to a
wider range of geometric types? ie: intersects with, contains, etc, to
work on paths and polygons.
- is the developer who originated the types and operators still attached
to the project? would there be any appetite for doing some extensions or
indexing improvements if funding could be found? (I may be able to bring
some funding to bear to support this kind of development)

Thanks,
Paul

-- 
  __
 /
 | Paul Ramsey
 | Refractions Research
 | Email: [EMAIL PROTECTED]
 | Phone: (250) 885-0632
 \_

[GENERAL] Support for images?

[Gilley, Charles H.]

Does Postgres support images or binary files in a database?
Is this what the large objects are for?

chg