[GENERAL] Zero-length character breaking query?

[Doug Gorley]

G'day,

I believe I've got some bad data in a table, but I'm not sure how it got there, 
or how this scenario is possible.

The table is called tdt_unsent.  The field is str_name_l.  For demonstration 
purposes,  the value is "SMITH".

"select * from tdt_unsent where str_name_l = 'SMITH'" returns 0 rows.
"select * from tdt_unsent where str_name_l ~ '^SMITH'" returns 3 rows.
"select * from tdt_unsent where str_name_l ~ '^SMITH$'" returns 0 rows.
"select length(str_name_l) from tdt_unsent where str_name_l ~ '^SMITH'" returns 
"5".

So, it's as if there is a zero-length character at the end of the value that is 
preventing a match.  Is this possible?  If so, how could this data have been 
created?

Thanks,

Doug Gorley
dgor...@aihs.ca 


-- 
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] A 154 GB table swelled to 527 GB on the Slony slave. How to compact it?

[Greg Williamson]

Filip Rembiałkowski suggested:


>> On Thu, Mar 15, 2012 at 6:43 AM, Aleksey Tsalolikhin 
>>  wrote:
>> 
>> 
>> Is there any way to consolidate the pages on the slave without taking
>> replication offline?
>> 
>> 
>maybe CLUSTER?
>
<...>
>
>Of course events destined to this table will be queued by Slony while the 
>table is locked.
>

I've not much recent experience with Slony, but possibly pg_reorg, found at:

  

would be of use ...

Greg Williamson

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

[GENERAL] Problem for restoure data base Postgre

[BrunoSteven]

I am trying restoure data  base from Postgre running on Windows Server 2003 
32bits with Postgre with 9.0.3 for a Linux running Centos 6 with Postgre 9.0.7 
, but wasn´t  working very well . 

I post follow link for paste bin with some message error 

http://pastebin.com/94qnc8Hj

I don´t understand very well functions Postgre, but I need help for solve this 
problem. 

Thank a lot. 


-- 
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] Problem for restoure data base Postgre

[Alban Hertroys]

On 16 Mar 2012, at 24:53, BrunoSteven wrote:

> I am trying restoure data  base from Postgre running on Windows Server 2003 
> 32bits with Postgre with 9.0.3 for a Linux running Centos 6 with Postgre 
> 9.0.7 , but wasn´t  working very well . 
> 
> I post follow link for paste bin with some message error 
> 
> http://pastebin.com/94qnc8Hj
> 
> I don´t understand very well functions Postgre, but I need help for solve 
> this problem. 


You may want to check the preferred spelling of the product, it's definitely 
not Postgre.

It looks like you're using an old version of pg_restore to attempt the restore. 
CREATE EXTENSION is new to Postgres 9.x, so your pg_restore is probably from a 
Postgres 8.x installation.

Alban Hertroys

--
Screwing up is an excellent way to attach something to the ceiling.


-- 
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] Optimise PostgreSQL for fast testing

[Simon Riggs]

On Fri, Mar 16, 2012 at 4:38 AM, Dmytrii Nagirniak  wrote:

> To "fix" it I open a transaction before each test and roll it back at the
> end.
>
> Some numbers for ~700 tests.
>
> - Truncation: SQLite - 34s, PG - 76s.
> - Transaction: SQLite - 17s, PG - 18s.
>
> 2x speed increase for SQLite.
> 4x speed increase for PG.
>
> Hope that'll help some of you.

Did you try this? synchronous_commit = off

-- 
 Simon Riggs   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services

-- 
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] how to measure wal_buffer usage

[Albe Laurenz]

Lonni J Friedman wrote:
> After reading this interesting article on shared_buffers and wal_buffers:
> http://rhaas.blogspot.com/2012/03/tuning-sharedbuffers-and-walbuffers.html
> 
> it got me wondering if my settings were ideal.  Is there some way to
> measure wal_buffer usage in real time, so that I could simply monitor
> it for some period of time, and then come up with a way of determining
> if the current setting is sufficient?
> 
> I tried googling, but every reference that I've found simply defaults
> to the "trial & error" approach to performance tuning.

You can use the contrib module pg_buffercache to inspect the shared buffers.
If almost all your shared buffers have high use count (4 or 5),
shared_buffers may be too small.  If not, consider reducing shared_buffers.

It's probably better to start with a moderate value and tune upwards.

You can also look at pg_statio_all_tables and pg_statio_all_indexes and
calculate the buffer hit ratio.  If that is low, that's also an indication
that shared_buffers is too small.

You should distinguish between tables and indexes:
it is usually more important that indexes are cached.

Try to observe these things over time, for example by taking
snapshots every n minutes and storing the results in a table.

Yours,
Laurenz Albe

-- 
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] Zero-length character breaking query?

[David Johnston]

On Mar 15, 2012, at 19:09, Doug Gorley  wrote:

> G'day,
> 
> I believe I've got some bad data in a table, but I'm not sure how it got 
> there, or how this scenario is possible.
> 
> The table is called tdt_unsent.  The field is str_name_l.  For demonstration 
> purposes,  the value is "SMITH".
> 
> "select * from tdt_unsent where str_name_l = 'SMITH'" returns 0 rows.
> "select * from tdt_unsent where str_name_l ~ '^SMITH'" returns 3 rows.
> "select * from tdt_unsent where str_name_l ~ '^SMITH$'" returns 0 rows.
> "select length(str_name_l) from tdt_unsent where str_name_l ~ '^SMITH'" 
> returns "5".
> 
> So, it's as if there is a zero-length character at the end of the value that 
> is preventing a match.  Is this possible?  If so, how could this data have 
> been created?
> 
> Thanks,
> 
> Doug Gorley
> dgor...@aihs.ca 
> 

Try (in the regexp) adding '\r?\n' after SMITH and see what happens.

How did you enter the SMITH record into the table in the first place?

David J.
-- 
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] Zero-length character breaking query?

[Tom Lane]

Doug Gorley  writes:
> The table is called tdt_unsent.  The field is str_name_l.  For demonstration 
> purposes,  the value is "SMITH".

> "select * from tdt_unsent where str_name_l = 'SMITH'" returns 0 rows.
> "select * from tdt_unsent where str_name_l ~ '^SMITH'" returns 3 rows.
> "select * from tdt_unsent where str_name_l ~ '^SMITH$'" returns 0 rows.
> "select length(str_name_l) from tdt_unsent where str_name_l ~ '^SMITH'" 
> returns "5".

I'd check EXPLAIN (with the actual problematic string, not SMITH).
The planner is probably trying to build an index range condition from
the regex pattern --- is it doing the right thing given your locale?

If the plan looks okay, maybe you need to reindex whatever index it's
using.

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] Problem for restoure data base Postgre

[Tom Lane]

Alban Hertroys  writes:
> On 16 Mar 2012, at 24:53, BrunoSteven wrote:
>> I am trying restoure data  base from Postgre running on Windows Server 2003 
>> 32bits with Postgre with 9.0.3 for a Linux running Centos 6 with Postgre 
>> 9.0.7 , but wasn´t  working very well . 
>> 
>> I post follow link for paste bin with some message error 
>> 
>> http://pastebin.com/94qnc8Hj
>> 
>> I don´t understand very well functions Postgre, but I need help for solve 
>> this problem. 

> You may want to check the preferred spelling of the product, it's definitely 
> not Postgre.

> It looks like you're using an old version of pg_restore to attempt the 
> restore. CREATE EXTENSION is new to Postgres 9.x, so your pg_restore is 
> probably from a Postgres 8.x installation.

More to the point, CREATE EXTENSION is new as of 9.1.  So that dump did
not come from a 9.0.x server, and you're not going to be able to restore
it into a 9.0.x server.

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] serial- sequence priveleges

[salah jubeh]

Hello,


When creating a serial, a sequence is created automatically.
CREATE TABLE tablename ( colname SERIAL
);
CREATE SEQUENCE tablename_colname_seq;
CREATE TABLE tablename ( colname integer NOT NULL DEFAULT 
nextval('tablename_colname_seq')
);
ALTER SEQUENCE tablename_colname_seq OWNED BY tablename.colname;

Should not  a proper permissions based on the table privileges added to the 
sequence ?. For example, when a table has INSERT, UPDATE permissions on the 
table  to a certain user, this should be taken into account. 

Regards  

Re: [GENERAL] Temporal foreign keys

[Andreas Kretschmer]

Jeff Davis  wrote:

> On Fri, 2012-02-03 at 07:58 +0100, Matthias wrote:
> > Hey,
> > 
> > how can I implement temporal foreign keys with postgresql? Is writing
> > triggers the only way to enforce temporal referential integrity
> > currently?
> 
> Yes, currently that's the only way. Look at CREATE CONSTRAINT TRIGGER.

It works in 9.2devel ;-)

test=# create table x (d daterange primary key);
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "x_pkey"
for table "x"
CREATE TABLE
test=*# create table y (d daterange references x);
CREATE TABLE
test=*# insert into x values ('[2012-01-01,2012-01-10)');
INSERT 0 1
test=*# insert into y values ('[2012-01-01,2012-01-10)');
INSERT 0 1
test=*# insert into y values ('[2012-01-01,2012-01-20)');
ERROR:  insert or update on table "y" violates foreign key constraint "y_d_fkey"
DETAIL:  Key (d)=([2012-01-01,2012-01-20)) is not present in table "x".



Jeff: thx for YOUR work!


Andreas
-- 
Really, I'm not out to destroy Microsoft. That will just be a completely
unintentional side effect.  (Linus Torvalds)
"If I was god, I would recompile penguin with --enable-fly."   (unknown)
Kaufbach, Saxony, Germany, Europe.  N 51.05082°, E 13.56889°

-- 
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] how to measure wal_buffer usage

[Lonni J Friedman]

On Fri, Mar 16, 2012 at 2:45 AM, Albe Laurenz  wrote:
> Lonni J Friedman wrote:
>> After reading this interesting article on shared_buffers and wal_buffers:
>> http://rhaas.blogspot.com/2012/03/tuning-sharedbuffers-and-walbuffers.html
>>
>> it got me wondering if my settings were ideal.  Is there some way to
>> measure wal_buffer usage in real time, so that I could simply monitor
>> it for some period of time, and then come up with a way of determining
>> if the current setting is sufficient?
>>
>> I tried googling, but every reference that I've found simply defaults
>> to the "trial & error" approach to performance tuning.
>
> You can use the contrib module pg_buffercache to inspect the shared buffers.
> If almost all your shared buffers have high use count (4 or 5),
> shared_buffers may be too small.  If not, consider reducing shared_buffers.

pg_buffercache only reports on the buffer_cache, it does not report
any data on the wal_cache.

>
> It's probably better to start with a moderate value and tune upwards.
>
> You can also look at pg_statio_all_tables and pg_statio_all_indexes and
> calculate the buffer hit ratio.  If that is low, that's also an indication
> that shared_buffers is too small.

Isn't this also specific to the buffer_cache rather than the wal_cache?

>
> You should distinguish between tables and indexes:
> it is usually more important that indexes are cached.
>
> Try to observe these things over time, for example by taking
> snapshots every n minutes and storing the results in a table.
>
> Yours,
> Laurenz Albe



-- 
~
L. Friedman                                    netll...@gmail.com
LlamaLand                       https://netllama.linux-sxs.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] serial- sequence priveleges

[Adrian Klaver]

On 03/16/2012 07:06 AM, salah jubeh wrote:

Hello,

When creating a serial, a sequence is created automatically.

CREATE TABLEtablename  (
 colname  SERIAL
);

CREATE SEQUENCE tablename_colname_seq;
CREATE TABLE tablename (
 colname integer NOT NULL DEFAULT nextval('tablename_colname_seq')
);
ALTER SEQUENCE tablename_colname_seq OWNED BY tablename.colname;


The OWNED BY is for dependency tracking not privileges:

http://www.postgresql.org/docs/9.1/interactive/sql-altersequence.html
OWNED BY table.column
OWNED BY NONE
The OWNED BY option causes the sequence to be associated with a specific 
table column, such that if that column (or its whole table) is dropped, 
the sequence will be automatically dropped as well. If specified, this 
association replaces any previously specified association for the 
sequence. The specified table must have the same owner and be in the 
same schema as the sequence. Specifying OWNED BY NONE removes any 
existing association, making the sequence "free-standing".







Should not a proper permissions based on the table privileges added to
the sequence ?. For example, when a table has INSERT, UPDATE permissions
on the table to a certain user, this should be taken into account.

Regards





--
Adrian Klaver
adrian.kla...@gmail.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] how to measure wal_buffer usage

[Albe Laurenz]

Lonni J Friedman wrote:
>>> After reading this interesting article on shared_buffers and wal_buffers:
>>> http://rhaas.blogspot.com/2012/03/tuning-sharedbuffers-and-walbuffers.html
>>>
>>> it got me wondering if my settings were ideal.  Is there some way to
>>> measure wal_buffer usage in real time, so that I could simply monitor
>>> it for some period of time, and then come up with a way of determining
>>> if the current setting is sufficient?
>>>
>>> I tried googling, but every reference that I've found simply defaults
>>> to the "trial & error" approach to performance tuning.
>>
>> You can use the contrib module pg_buffercache to inspect the shared buffers.
>> If almost all your shared buffers have high use count (4 or 5),
>> shared_buffers may be too small.  If not, consider reducing shared_buffers.
> 
> pg_buffercache only reports on the buffer_cache, it does not report
> any data on the wal_cache.

You are right.

>> It's probably better to start with a moderate value and tune upwards.
>>
>> You can also look at pg_statio_all_tables and pg_statio_all_indexes and
>> calculate the buffer hit ratio.  If that is low, that's also an indication
>> that shared_buffers is too small.
>
> Isn't this also specific to the buffer_cache rather than the wal_cache?

Correct.

I don't know how to inspect usage WAL cache usage.

Yours,
Laurenz Albe

-- 
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] serial- sequence priveleges

[Adrian Klaver]

On 03/16/2012 08:00 AM, salah jubeh wrote:

Hello Adrian,

Sorry, I was not clear.

what I meant is that.
GRANT INSERT, UPDATE ON tablenaem TO USER; -- leads to GRANT ALL ON
TABLE tablename_colname_seq TO USER


CCing the list.
Still not following.
What version of Postgres are you using?

Using 9.0.7 here I get:

test=> CREATE TABLE ser_test(id serial);

public | ser_test  | table| aklaver
public | ser_test_id_seq   | sequence | aklaver

test=> \dp ser_test
Access privileges
 Schema |   Name   | Type  | Access privileges | Column access privileges
+--+---+---+--
 public | ser_test | table |   |
(1 row)

test=> \dp ser_test_id_seq
 Access privileges
 Schema |  Name   |   Type   | Access privileges | Column 
access privileges

+-+--+---+--
 public | ser_test_id_seq | sequence |


GRANT INSERT, UPDATE ON table ser_test to sales;
GRANT


test=> \dp ser_test
   Access privileges
 Schema |   Name   | Type  |Access privileges| Column access 
privileges

+--+---+-+--
 public | ser_test | table | aklaver=arwdDxt/aklaver+|
|  |   | sales=aw/aklaver|

test=> \dp ser_test_id_seq
 Access privileges
 Schema |  Name   |   Type   | Access privileges | Column 
access privileges

+-+--+---+--
 public | ser_test_id_seq | sequence |   |





Regards




--
Adrian Klaver
adrian.kla...@gmail.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] serial- sequence priveleges

[Adrian Klaver]

On 03/16/2012 08:00 AM, salah jubeh wrote:

Hello Adrian,

Sorry, I was not clear.

what I meant is that.
GRANT INSERT, UPDATE ON tablenaem TO USER; -- leads to GRANT ALL ON
TABLE tablename_colname_seq TO USER


Another thought you do not happen to have DEFAULT PRIVILEGES set up for 
sequences:


http://www.postgresql.org/docs/9.0/interactive/sql-alterdefaultprivileges.html

In psql \ddp will list them.



Regards




--
Adrian Klaver
adrian.kla...@gmail.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] Windows 7 Compatibility

[Wong, Beverly]

I work with Philadelphia Gas Works & would like to know if Postgresql
v8.0.3 is compatible with Windows 7 OS.

 

Thank You

 

Beverly T. Wong I Ext: 6026

Technical Writing Intern 

Philadelphia Gas Works 

 

Re: [GENERAL] Problem for restoure data base Postgre

[aspenbr]

Alban,

Fist Was installed postgre 8 on Centos after I remove this version and install 
postgre 9 . Maybe there are rest of installation of version 8 . 

Thank you 

Sent from my iPhone

On 16/03/2012, at 05:07, Alban Hertroys  wrote:

> On 16 Mar 2012, at 24:53, BrunoSteven wrote:
> 
>> I am trying restoure data  base from Postgre running on Windows Server 2003 
>> 32bits with Postgre with 9.0.3 for a Linux running Centos 6 with Postgre 
>> 9.0.7 , but wasn´t  working very well . 
>> 
>> I post follow link for paste bin with some message error 
>> 
>> http://pastebin.com/94qnc8Hj
>> 
>> I don´t understand very well functions Postgre, but I need help for solve 
>> this problem. 
> 
> 
> You may want to check the preferred spelling of the product, it's definitely 
> not Postgre.
> 
> It looks like you're using an old version of pg_restore to attempt the 
> restore. CREATE EXTENSION is new to Postgres 9.x, so your pg_restore is 
> probably from a Postgres 8.x installation.
> 
> Alban Hertroys
> 
> --
> Screwing up is an excellent way to attach something to the ceiling.
> 

-- 
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] Problem for restoure data base Postgre

[Scott Marlowe]

On Fri, Mar 16, 2012 at 5:21 AM,   wrote:
> Alban,
>
> Fist Was installed postgre 8 on Centos after I remove this version and 
> install postgre 9 . Maybe there are rest of installation of version 8 .

That doesn't explain how you wound up with a dump created by 9.1
though. (assuming you installed 9.0 and not 9.1)

-- 
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] Windows 7 Compatibility

[m...@trausch.us]

On 03/16/2012 10:08 AM, Wong, Beverly wrote:
> I work with Philadelphia Gas Works & would like to know if Postgresql
> v8.0.3 is compatible with Windows 7 OS.

First, you shouldn't be considering the user of PostgreSQL version 8.0;
starting with the release of PostgreSQL 8.3, versions 8.0 and 8.1 are no
longer officially supported on Microsoft Windows of any version, flavor
or variant.[0]

Additionally, according to [1] the oldest release series that still has
support is 8.3, which will continue to be supported for only about the
next 11 months.  You should be using 8.3 at a minimum, and unless there
are really good reasons to do otherwise, you should probably use the
most recent release series, 9.1, which was first released in September
of 2011.

--- Mike

[0] http://www.postgresql.org/about/news/865/
[1] http://www.postgresql.org/support/versioning/

-- 
A man who reasons deliberately, manages it better after studying Logic
than he could before, if he is sincere about it and has common sense.
   --- Carveth Read, “Logic”



signature.asc
Description: OpenPGP digital signature

Re: [GENERAL] Windows 7 Compatibility

[m...@trausch.us]

On 03/16/2012 10:08 AM, Wong, Beverly wrote:
> I work with Philadelphia Gas Works & would like to know if
> Postgresql v8.0.3 is compatible with Windows 7 OS.

Oops, forgot to address the Windows 7 component of your question in the
previous reply.

According to [0], PostgreSQL is not supported on Windows XP Embedded,
Windows 2000, Windows NT, Windows 9x, Windows 3.x, Windows CE and
Windows Mobile.

Also, on the same page:

> What versions of Windows does PostgreSQL run on?
> 
> PostgreSQL is supported on Windows XP and above, at least as of
> version 9.0. It will run on 32 and 64 bit systems.
> 
> Versions of the server are not tested on new operating system
> versions that are released after a newer major version of the server
> was released. For example, Windows 7 was released after PostgreSQL
> 8.4, so PostgreSQL 8.3 will not be supported on it. Similarly, when
> the upcoming RHEL 6 is released, only PostgreSQL 9.0.x will be
> supported on it. We aim to support new versions of Windows in the
> PostgreSQL major version following their release at the latest.
> 
> For information about platforms supported by the One-click installer,
> please see the installer download page, off the main download page
> for windows.

Hope this helps.

--- Mike

[0] http://is.gd/8RJ1s5

-- 
A man who reasons deliberately, manages it better after studying Logic
than he could before, if he is sincere about it and has common sense.
   --- Carveth Read, “Logic”



signature.asc
Description: OpenPGP digital signature

Re: [GENERAL] Zero-length character breaking query?

[Peter Bex]

On Thu, Mar 15, 2012 at 05:09:32PM -0600, Doug Gorley wrote:
> G'day,
> 
> "select * from tdt_unsent where str_name_l = 'SMITH'" returns 0 rows.
> "select * from tdt_unsent where str_name_l ~ '^SMITH'" returns 3 rows.
> "select * from tdt_unsent where str_name_l ~ '^SMITH$'" returns 0 rows.
> "select length(str_name_l) from tdt_unsent where str_name_l ~ '^SMITH'" 
> returns "5".

What does octet_length return?  Perhaps this is some nonprintable
control character.  Unicode is full of those.  If it differs from
the string length, then you can be pretty sure that's the case.

Cheers,
Peter
-- 
http://sjamaan.ath.cx
--
"The process of preparing programs for a digital computer
 is especially attractive, not only because it can be economically
 and scientifically rewarding, but also because it can be an aesthetic
 experience much like composing poetry or music."
-- Donald Knuth

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

[GENERAL] Authenticating from a web service call

[Bryan Montgomery]

Hello,
We are looking at implementing a web service that basically makes calls to
the database.

I have been thinking about ways to secure the web service based on the
database.

I initially thought about just connecting to the database as the user with
parameters passed through the web service - however I don't know how to do
that other than clear text passwords.

So, is it possible for clients to encrypt their password and pass that
through the web service to the database? I was looking at the way postgres
stores the users passwords but first of all I'm not sure if that is
something the client could do. Then, if they could, how to go about
connecting as a system user and verifying that the userid and password
provided by the client are correct.

I could just provide another table with an encrypted password using a
specified encryption process that the client can replicate and provide
through the web service.

Hopefully this makes sense :)

Bryan.

Re: [GENERAL] Authenticating from a web service call

[Raymond O'Donnell]

On 16/03/2012 18:39, Bryan Montgomery wrote:
> Hello,
> We are looking at implementing a web service that basically makes calls
> to the database.
> 
> I have been thinking about ways to secure the web service based on the
> database.
> 
> I initially thought about just connecting to the database as the user
> with parameters passed through the web service - however I don't know
> how to do that other than clear text passwords.

Postgres supports connections over SSL - will this do the job?

http://www.postgresql.org/docs/9.1/static/ssl-tcp.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] Zero-length character breaking query?

[Bosco Rama]

Doug Gorley wrote:
> 
> "select * from tdt_unsent where str_name_l = 'SMITH'" returns 0 rows.
> "select * from tdt_unsent where str_name_l ~ '^SMITH'" returns 3 rows.
> "select * from tdt_unsent where str_name_l ~ '^SMITH$'" returns 0 rows.
> "select length(str_name_l) from tdt_unsent where str_name_l ~ '^SMITH'" 
> returns "5".

what does the following return?
  select str_name_l::bytea from tdt_unsent where str_name_l ~ '^SMITH';

It should show you any chars that don't usually display in your locale as
hex chars (i.e. \xNN).

Bosco.

-- 
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] A 154 GB table swelled to 527 GB on the Slony slave. How to compact it?

[Aleksey Tsalolikhin]

On Thu, Mar 15, 2012 at 6:43 AM, Aleksey Tsalolikhin
 wrote:
> Is there any way to consolidate the pages on the slave without taking
> replication offline?

Filip Rembiałkowski suggested:   maybe CLUSTER?

Greg Williamson suggested: pg_reorg


Thank you, Filip and Greg.  They would both work IF I had enough free
space on the slave,
which, sadly, I do not. :(

CLUSTER requires free space at least equal to the sum of the table
size and the index sizes.

pg_reorg rquires amount of space twice larger than target table and indexes.

Too bad I can't say "CLUSTER TABLE tablename USING_ARRAY
/dev/temp/array" or something
like that, using an external array for temporary storage just for the
cluster.  I do have an external
USB drive with more than enough free space on it.

I've got a maintenance window scheduled for tomorrow night to get rid
of the holes in the pages on the
slave.  My plan is to shut down the application, destroy the
replication set, re-create it, and start
replication, which will do a full sync.  It's a litle overkill but I
have this procedure documented
and I've done it before.

I expect that after the table is TRUNCATE'd on the Slave and COPY'ied over from
the Master, we won't have holes in the pages   I sure hope so!!

Our database is about 200 GB - over a WAN link, last time it took 8
hours to do a full sync, I expect it'll be
more like 9 or 10 hours this time.

I still don't know where these holes came from... and what is
responsible.  Our Postgres 8.4.9 or
Slony 1.2.21 or some combination of the two?  We don't delete rows
from that table AFAIK so I'm
really confused why the page contents would be so sparse...  and since
I'm not fixing the root cause
I'm sure the problem will come back.   But at least this keeps my
slave operational (which it won't be
once the filesystem hits 100%)

Thanks for all the help and suggestions so far. I really appreciate it!

Any more pointers would be welcome...

Too bad there is no way to compact the rows/pages within the table
without using an
temporary table.  That would be the silver bullet for my particular
predicament.

Yours,
Aleksey

-- 
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] A 154 GB table swelled to 527 GB on the Slony slave. How to compact it?

[Steve Crawford]

I'm coming into this conversation *way* late so forgive me if this has 
been hashed out already


On 03/16/2012 12:20 PM, Aleksey Tsalolikhin wrote:
CLUSTER requires free space at least equal to the sum of the tablesize 
and the index sizes.




Although it is not documented in an absolutely clear way, I do not 
believe this is true in part due to the different cluster options and 
the lack of specificity on whether tablesize refers to the on-disk size 
of the new or the old table.


I *think* you can get away with only sufficient free space to store the 
*new* table and indexes which, on a heavily bloated table, may be 
significantly less than the space required for a full copy of the 
bloated table/indexes.


Depending on your schema and which tables are using space, you might be 
able to start clustering the smaller tables and progressively free 
additional space that can be used when clustering the larger tables.



Too bad there is no way to compact the rows/pages within the table
without using an
temporary table.  That would be the silver bullet for my particular
predicament.
Isn't that "VACUUM FULL"? Much slower than CLUSTER and can cause index 
bloat (solvable by subsequent reindexing) but does not create a 
duplicate of the table.


Cheers,
Steve


--
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] Temporal foreign keys

[Jeff Davis]

On Fri, 2012-03-16 at 15:13 +0100, Andreas Kretschmer wrote:
> > On Fri, 2012-02-03 at 07:58 +0100, Matthias wrote:
> > > how can I implement temporal foreign keys with postgresql? Is writing
> > > triggers the only way to enforce temporal referential integrity
> > > currently?
> > 
> It works in 9.2devel ;-)
> 
> test=# create table x (d daterange primary key);
> NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "x_pkey"
> for table "x"
> CREATE TABLE
> test=*# create table y (d daterange references x);
> CREATE TABLE
> test=*# insert into x values ('[2012-01-01,2012-01-10)');
> INSERT 0 1
> test=*# insert into y values ('[2012-01-01,2012-01-10)');
> INSERT 0 1
> test=*# insert into y values ('[2012-01-01,2012-01-20)');
> ERROR:  insert or update on table "y" violates foreign key constraint 
> "y_d_fkey"
> DETAIL:  Key (d)=([2012-01-01,2012-01-20)) is not present in table "x".

If I understand what he was asking for, it was a kind of "range foreign
key" which means that the following query should succeed:

  insert into y values ('[2012-01-02,2012-01-04)');

because that range is contained in a value in the table x.

So it's slightly different semantics than a normal foreign key.

But yes, normal foreign keys (based on equality) work fine over range
types.

Regards,
Jeff Davis


-- 
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] A 154 GB table swelled to 527 GB on the Slony slave. How to compact it?

[Alban Hertroys]

On 16 Mar 2012, at 20:20, Aleksey Tsalolikhin wrote:

> CLUSTER requires free space at least equal to the sum of the table
> size and the index sizes.
> 
> pg_reorg rquires amount of space twice larger than target table and indexes.
> 
> Too bad I can't say "CLUSTER TABLE tablename USING_ARRAY
> /dev/temp/array" or something
> like that, using an external array for temporary storage just for the
> cluster.  I do have an external
> USB drive with more than enough free space on it.


You could move the table to a different TABLESPACE, namely one on that USB 
disk. I'm not particular to the intrinsics of tablespaces, but it's possible 
that moving it would trigger a rewrite of the contents such that the effect 
would be similar to what CLUSTER would do for you - it probably just moves the 
files though, in which case you'd perform the CLUSTER on the new TABLESPACE and 
then move it back.

Alban Hertroys

--
Screwing up is an excellent way to attach something to the ceiling.


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

[GENERAL] Query

[prem tolani]

Hi,

I am using postgresql-8.1 in my application. When I restart the application, I 
am getting error

PG "FATAL:� could not reattach to shared memory (key=5432001, addr=0210): 
Invalid
argument.

I would appreciate, if you can provide some inputs.

Regards,
Prem



-- 
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] A 154 GB table swelled to 527 GB on the Slony slave. How to compact it?

[Scott Marlowe]

On Fri, Mar 16, 2012 at 1:20 PM, Aleksey Tsalolikhin
 wrote:
> On Thu, Mar 15, 2012 at 6:43 AM, Aleksey Tsalolikhin
>  wrote:
>> Is there any way to consolidate the pages on the slave without taking
>> replication offline?
>
> Filip Rembiałkowski suggested:   maybe CLUSTER?
>
> Greg Williamson suggested: pg_reorg
>
>
> Thank you, Filip and Greg.  They would both work IF I had enough free
> space on the slave,
> which, sadly, I do not. :(
>
> CLUSTER requires free space at least equal to the sum of the table
> size and the index sizes.
>
> pg_reorg rquires amount of space twice larger than target table and indexes.
>
> Too bad I can't say "CLUSTER TABLE tablename USING_ARRAY
> /dev/temp/array" or something
> like that, using an external array for temporary storage just for the
> cluster.  I do have an external
> USB drive with more than enough free space on it.
>
> I've got a maintenance window scheduled for tomorrow night to get rid
> of the holes in the pages on the
> slave.  My plan is to shut down the application, destroy the
> replication set, re-create it, and start
> replication, which will do a full sync.  It's a litle overkill but I
> have this procedure documented
> and I've done it before.

If you've got other big tables in the set, you could put that one
table into its own set, then drop that set and resubscribe a new set
with just that table in it, then merge the sets.

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

[Adrian Klaver]

On 03/16/2012 02:38 PM, prem tolani wrote:

Hi,

I am using postgresql-8.1 in my application. When I restart the application, I 
am getting error

PG "FATAL:� could not reattach to shared memory (key=5432001, addr=0210): 
Invalid
argument.

I would appreciate, if you can provide some inputs.


Would this be on a Windows machine?



Regards,
Prem






--
Adrian Klaver
adrian.kla...@gmail.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] pg_upgrade and statistics generation

[Bruce Momjian]

I have posted a blog entry about how to get pg_upgrade to generate
useful optimizer statistics more quickly once it finishes:

http://momjian.us/main/blogs/pgblog/2012.html#March_16_2012

I have also provided scripts that can be used with pg_upgrade 9.1 and
earlier, so feel free to try them out.

-- 
  Bruce Momjian  http://momjian.us
  EnterpriseDB http://enterprisedb.com

  + It's impossible for everything to be true. +

-- 
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] A 154 GB table swelled to 527 GB on the Slony slave. How to compact it?

[Aleksey Tsalolikhin]

On Fri, Mar 16, 2012 at 2:03 PM, Steve Crawford
 wrote:
>
>
> I *think* you can get away with only sufficient free space to store the
> *new* table and indexes

Yeah; I don't have that much free space.  Just 30 GB short.  :(


> Depending on your schema and which tables are using space, you might be able
> to start clustering the smaller tables and progressively free additional
> space that can be used when clustering the larger tables.

Clever idea, thank you.  Unfortunately the usage is 95%+ in this one big table.


>> Too bad there is no way to compact the rows/pages within the table
>> without using an
>> temporary table.  That would be the silver bullet for my particular
>> predicament.
>
> Isn't that "VACUUM FULL"? Much slower than CLUSTER and can cause index bloat
> (solvable by subsequent reindexing) but does not create a duplicate of the
> table.

I *did* run "VACUUM FULL" on the table, but I still have holes in my pages.
(i.e. examining a page using "select ctid" shows me row 2, 8, 15, 20, etc.
not 1 2 3 4 ...)

Is this a problem with "VACUUM FULL" or with my understanding?

Thank you, Scott, for the suggestion to move the big table to a separate
Slony replication set; and to Alban for moving it to a different TABLESPACE.
That is especially interesting, and I think exactly what I was looking for
(rewrite the table using an external storage device).  Unfortunately I am out
of time to resolve this "live"; we'll take things down tomorrow night
(I only get
Saturday nights for maintenance, and not too often, either) and just re-sync.

This has been extremely educational; thank you!

And I still don't know why I have holes in my pages...  should I have holes
in my pages??  why only on the slave and not on the master?  (the slave has
4x the number of pages compared to the master, and there's less rows per
page on the slave.)

Yours very truly,
Aleksey

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

[Tom Lane]

prem tolani  writes:
> I am using postgresql-8.1 in my application. When I restart the application, 
> I am getting error

> PG "FATAL:� could not reattach to shared memory (key=5432001, 
> addr=0210): Invalid
> argument.

This was fixed in 8.3.something.  8.1 has been out of support for awhile
now.

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