Re: [GENERAL] help

[Martijn van Oosterhout]

On Thu, Aug 25, 2005 at 04:10:27PM -0700, Matt A. wrote:
> Anyway, I am new to postgres and looking to casts. I
> have wrote this question several times before and on
> this version I forgot to add the exact rules of why
> it's breaking and what i need it to do. I will try to
> be more clear... 
> 
> I use the rules to insert 0/1/null inserts into
> booleans, integers, text, etc. fields.
> 
> Example: insert into table (bool_column) values
> nullif('',''));
> ERROR: column "bool_column" is of type boolean but 
> expression is of type text.

First rule, the type of a functions is determined by its arguments.
Neither of the arguments in your example are boolean so it doesn't know
you want a boolean. nullif itself works on any type.

kleptog=# create temp table x (t text, b bool, i integer);
CREATE TABLE
kleptog=# insert into x values (nullif('1','1'), nullif(true,true),
nullif(4,4) );
INSERT 114774 1
kleptog=# select * from x;
 t | b | i 
---+---+---
   |   |  
(1 row)

All nulls...

Your choice are to indicate in the arguments what type you want. In
this case the arguments arn't booleans so that won't work. Your other
option is to cast the result, which you can't because text -> bool is
not a valid typecast.

> 
> Where '' == '' should evaluate to NULL as OPAQUE
> (depreciated) or similiar return instead of TEXT cast.
> 
> So if I could alter the cast from text to return
> either INTEGER or TEXT, that would super! I'm not sure
> if it's possible but if so I'm willing to do what it
> takes to accomplish it. 

Functions don't return OPAQUE, they can't because a function knows
exactly what it's returning. In your case it's returning TEXT because
that's what the arguments default to if it doesn't know any better.

Technically, nullif takes arguments (anyelement,anyelement) which means
it can take any type, as long as they're the same.

Hope this clarifies it for you.
-- 
Martijn van Oosterhout  http://svana.org/kleptog/
> Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
> tool for doing 5% of the work and then sitting around waiting for someone
> else to do the other 95% so you can sue them.


pgpCWVKN5DKpB.pgp
Description: PGP signature

Re: [GENERAL] unsubscribe

[felix]

On Thu, Aug 25, 2005 at 07:30:07AM +0800, Richard Sydney-Smith wrote:
> unsubscribe

Here's how to unsubscribe:

First, ask your Internet Provider to mail you an Unsubscribing Kit.
Then follow these directions.

The kit will most likely be the standard no-fault type. Depending on
requirements, System A and/or System B can be used. When operating
System A, depress lever and a plastic dalkron unsubscriber will be
dispensed through the slot immediately underneath. When you have
fastened the adhesive lip, attach connection marked by the large "X"
outlet hose. Twist the silver-coloured ring one inch below the
connection point until you feel it lock.

The kit is now ready for use. The Cin-Eliminator is activated by the
small switch on the lip.  When securing, twist the ring back to its
initial condition, so that the two orange lines meet.  Disconnect.
Place the dalkron unsubscriber in the vacuum receptacle to the rear.
Activate by pressing the blue button.

The controls for System B are located on the opposite side. The red
release switch places the Cin-Eliminator into position; it can be
adjusted manually up or down by pressing the blue manual release
button. The opening is self-adjusting. To secure after use, press the
green button, which simultaneously activates the evaporator and
returns the Cin-Eliminator to its storage position.

You may log off if the green exit light is on over the evaporator.  If
the red light is illuminated, one of the Cin-Eliminator requirements
has not been properly implemented. Press the "List Guy" call button on
the right of the evaporator. He will secure all facilities from his
control panel.

To use the Auto-Unsub, first undress and place all your clothes in the
clothes rack. Put on the velcro slippers located in the cabinet
immediately below. Enter the shower, taking the entire kit with
you. On the control panel to your upper right upon entering you will
see a "Shower seal" button. Press to activate. A green light will then
be illuminated immediately below. On the intensity knob, select the
desired setting. Now depress the Auto-Unsub activation lever. Bathe
normally.

The Auto-Unsub will automatically go off after three minutes unless
you activate the "Manual off" override switch by flipping it up. When
you are ready to leave, press the blue "Shower seal" release
button. The door will open and you may leave. Please remove the velcro
slippers and place them in their container.

If you prefer the ultrasonic log-off mode, press the indicated blue
button. When the twin panels open, pull forward by rings A & B. The
knob to the left, just below the blue light, has three settings, low,
medium or high. For normal use, the medium setting is suggested.

After these settings have been made, you can activate the device by
switching to the "ON" position the clearly marked red switch. If
during the unsubscribing operation you wish to change the settings,
place the "manual off" override switch in the "OFF" position. You may
now make the change and repeat the cycle. When the green exit light
goes on, you may log off and have lunch. Please close the door behind
you.


-- 
... _._. ._ ._. . _._. ._. ___ .__ ._. . .__. ._ .. ._.
 Felix Finch: scarecrow repairman & rocket surgeon / [EMAIL PROTECTED]
  GPG = E987 4493 C860 246C 3B1E  6477 7838 76E9 182E 8151 ITAR license #4933
I've found a solution to Fermat's Last Theorem but I see I've run out of room o

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

Re: [GENERAL] POSS. FEATURE REQ: "Dynamic" Views

[Tom Lane]

"Jim C. Nasby" <[EMAIL PROTECTED]> writes:
> ... since currently CREATE VIEW v AS SELECT * FROM t
> actually expands the * out at creation time.

I believe that that behavior is required by the SQL spec.  For instance,
SQL92's description of ALTER TABLE ADD COLUMN sez:

Note: The addition of a column to a table has no effect on any
existing  included in a view descriptor or
 included in constraint descriptor because
any implicit s in these clauses are replaced
by explicit s when the clause is originally
evaluated. See the Syntax Rules of Subclause 7.10, "".

regards, tom lane

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

Re: [GENERAL] POSS. FEATURE REQ: "Dynamic" Views

[Ian Harding]

Brand X simulates this in their GUI diagrammer by tracking
dependencies and dropping and  recreating dependent views on schema
changes.  This might be a better job for one of the GUI tools for us
too, rather than trying to put it in the back end.  Brand X doesn't do
it in their backend either.

On 8/25/05, Jim C. Nasby <[EMAIL PROTECTED]> wrote:
> On Thu, Aug 25, 2005 at 03:03:30PM -0700, CSN wrote:
> > For lack of a better term, but I was curious if there
> > is/was any thought about making PG's views
> > automatically "see" changes in underlying tables, as
> > opposed to currently having to drop/create all
> > corresponding views if a table's structure (add/delete
> > fields, etc.) is changed.
> 
> There's not currently a TODO for this, no.
> http://www.postgresql.org/docs/faqs.TODO.html I'm not sure how hard it
> would be to do, since currently CREATE VIEW v AS SELECT * FROM t
> actually expands the * out at creation time.
> --
> Jim C. Nasby, Sr. Engineering Consultant  [EMAIL PROTECTED]
> Pervasive Softwarehttp://pervasive.com512-569-9461
> 
> ---(end of broadcast)---
> TIP 2: Don't 'kill -9' the postmaster
>

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

Re: [GENERAL] Its Beta Time Again ... 8.1 Beta 1 Now Available

[Tony Caduto]

Hi Marc,
Could you post the location of the beta documentation?  I seem to have 
lost the link :-(

Is there info on the new role system and in/out params in the beta docs?


Thanks,

Tony


PostgreSQL 8.1 will bring in alot of fairly large features, including:

 - Improve concurrent access to the shared buffer cache
 - Add in-memory bitmaps which allows multiple indexes to be 
merged in

   a single query
 - Add two-phase commit
 - Create a new role system that replaces users and groups
 - Move /contrib/pgautovacuum into the main server
 - Add shared row level locks using SELECT ... FOR SHARE
 - many many others listed in the HISTORY file





---(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] Altering built-in functions cast

[Matt A.]

Anyone know how I could alter the cast of the nullif()
function directly to return INT?

We use NULLIF() for adding [1|0|null] according to the
evalution of nullif('x','') into integer columns.
Where x is an integer and sometimes a empty string,
which if it's an empty string (x='') then we add NULL
cause NULLIF says if '' == '' then return NULL

Thank you,
matthew



__ 
Do you Yahoo!? 
Yahoo! Mail - Helps protect you from nasty viruses. 
http://promotions.yahoo.com/new_mail

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

Re: [GENERAL] Postgresql replication

[Jeff Davis]

Matt Miller wrote:
>>>http://pgfoundry.org/projects/pgcluster/ which provides syncronous
>>>multi-master clustering.
>>
>>He specifically said that pgcluster did not work for him
>>because ...PGCluster requires that there be a load balancer and a
>>replicator centrally located managing the cluster. If a network
>>problem happens at the centralized location, it would bring down all
>>locations completely.
> 
> 
> I think the load balancer is an optional component.  Clients can connect
> either to a load balancer, or directly to a specific machine.

The replicator surely is not optional, and must be centralized.

> Also, I think pgcluster can operate async.  If the system runs in
> "normal" mode then the client gets a response as soon as the transaction
> is committed on the local machine.  In "reliable" mode the client waits
> for the commit to happen on all machines.
> 

Interesting. I suppose whatever works for your application is the right
answer for replication. PGCluster is query-based, right? I suppose the
question would then be, would he rather use a trigger-based replication
 solution, like Slony, or a query-based replication solution.

Regards,
Jeff Davis

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

[GENERAL] Its Beta Time Again ... 8.1 Beta 1 Now Available

[Marc G. Fournier]

Back on the 1st of July, after almost 6 months of development since 8.0 
was released, development on 8.1 was frozen.


Now, after spending the past few weeks processing through outstanding 
patches applicable to 8.1, we now enter our beta testing period, where we 
need help from the community at large.


PostgreSQL 8.1 will bring in alot of fairly large features, including:

 - Improve concurrent access to the shared buffer cache
 - Add in-memory bitmaps which allows multiple indexes to be merged in
   a single query
 - Add two-phase commit
 - Create a new role system that replaces users and groups
 - Move /contrib/pgautovacuum into the main server
 - Add shared row level locks using SELECT ... FOR SHARE
 - many many others listed in the HISTORY file

As with all major releases, we need to put the above changes through some 
extensive testing, in as many scenarios as possible, so encourage both 
testing, as well as bug reports.


PostgreSQL 8.1 Beta 1 is now available via:

http://www.postgresql.org/ftp/source/v8.1beta

Please report any bugs with this beta to:

pgsql-bugs@postgresql.org

Or through our Bug Reporting Tool available at:

http://www.postgresql.org/support/submitbug




Marc G. Fournier   Hub.Org Networking Services (http://www.hub.org)
Email: [EMAIL PROTECTED]   Yahoo!: yscrappy  ICQ: 7615664

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

Re: [GENERAL] Postgresql replication

[Chris Travers]

William Yu wrote:

Another tidbit I'd like to add. What has helped a lot in implementing 
high-latency master-master replication writing our software with a 
business process model in mind where data is not posted directly to 
the final tables. Instead, users are generally allowed to enter 
anything -- could be incorrect, incomplete or the user does not have 
rights -- the data is still dumped into "pending" tables for people 
with rights to fix/review/approve later. Only after that process is 
the data posted to the final tables. (Good data entered on the first 
try still gets pended -- validation phase simply assumes the user who 
entered the data is also the one who fixed/reviewed/approved.)



In this case, why have multimaster replication at all?

Why not have the people who have rights to review this all write to the 
master database and have that replicated back?  It seems like latency is 
not really an issue.  Replication here is only going to complicate 
things.  If it were me, I would be having my approval app pull data from 
*all* of the databases independently and not rely on the replication for 
this part.  The replication could then be used to replicate *approved* 
data back to the slaves.




There may be a delay for the user if he/she is working on a server 
that doesn't have rights to post his data. However, the pending->post 
model gets users used to the idea of (1) entering all data in large 
swoop and validating/posting it afterwards and (2) data can/will sit 
in pending for a period of time until it is acted upon with 
somebody/some server with the proper authority. Hence users aren't 
expecting results to pop up on the screen the moment they press the 
submit button.



I still don't understand the purpose of replicating the pending data...

Best Wishes,
Chris Travers
Metatron Technology Consulting

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

Re: [GENERAL] help

[Matt A.]

Sorry to have replied directly to you. I assumed the
reply address was that of the pgsql email.A thousand
apologies.

Anyway, I am new to postgres and looking to casts. I
have wrote this question several times before and on
this version I forgot to add the exact rules of why
it's breaking and what i need it to do. I will try to
be more clear... 

I use the rules to insert 0/1/null inserts into
booleans, integers, text, etc. fields.

Example: insert into table (bool_column) values
nullif('',''));
ERROR: column "bool_column" is of type boolean but 
expression is of type text.

Where '' == '' should evaluate to NULL as OPAQUE
(depreciated) or similiar return instead of TEXT cast.

So if I could alter the cast from text to return
either INTEGER or TEXT, that would super! I'm not sure
if it's possible but if so I'm willing to do what it
takes to accomplish it. 

I'm on a test box to see if postgresql will be
suitable for our production environment (from MSSQL).
So I can route around and tear things apart at will. 

Even changing nullif() to return 1/0/null for boolean
and integer fields would be sufficient. It's hardly
used on TEXT casts. 

Thanks for your time. I hope that I may have been more
helpful on this reply. 

Thank you,
Matthew

--- Martijn van Oosterhout  wrote:

> Please reply to the list also in the future, I'm
> going to sleep now.
> Anyway, it still works:
> 
> kleptog=# create temp table x (a text);
> CREATE TABLE
> kleptog=# insert into x values (nullif('','')); 
> INSERT 114760 1
> kleptog=# insert into x values (nullif('1',''));
> INSERT 114761 1
> kleptog=# select a,a is null as isnull from x;
>  a | isnull 
> ---+
>| t
>  1 | f
> (2 rows)
> 
> Please show us the error you got.
> 
> Have a nice day,
> 
> 
> On Thu, Aug 25, 2005 at 02:57:29PM -0700, Matt A.
> wrote:
> > hi. I am speaking of INSERT not SELECT
> > 
> > --- Martijn van Oosterhout 
> wrote:
> > 
> > > Very odd, what are you trying?
> > > 
> > > kleptog=# select nullif('','') is null;
> > >  ?column? 
> > > --
> > >  t
> > > (1 row)
> > > 
> > > kleptog=# select nullif('1','') is null;
> > >  ?column? 
> > > --
> > >  f
> > > (1 row)
> > > 
> > > Works for me.
> > > 
> > > Have a nice day,
> > > 
> 
> -- 
> Martijn van Oosterhout 
> http://svana.org/kleptog/
> > Patent. n. Genius is 5% inspiration and 95%
> perspiration. A patent is a
> > tool for doing 5% of the work and then sitting
> around waiting for someone
> > else to do the other 95% so you can sue them.
> 




__ 
Yahoo! Mail 
Stay connected, organized, and protected. Take the tour: 
http://tour.mail.yahoo.com/mailtour.html 


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

Re: [GENERAL] POSS. FEATURE REQ: "Dynamic" Views

[Jim C. Nasby]

On Thu, Aug 25, 2005 at 03:03:30PM -0700, CSN wrote:
> For lack of a better term, but I was curious if there
> is/was any thought about making PG's views
> automatically "see" changes in underlying tables, as
> opposed to currently having to drop/create all
> corresponding views if a table's structure (add/delete
> fields, etc.) is changed.

There's not currently a TODO for this, no.
http://www.postgresql.org/docs/faqs.TODO.html I'm not sure how hard it
would be to do, since currently CREATE VIEW v AS SELECT * FROM t
actually expands the * out at creation time.
-- 
Jim C. Nasby, Sr. Engineering Consultant  [EMAIL PROTECTED]
Pervasive Softwarehttp://pervasive.com512-569-9461

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

Re: [GENERAL] help

[Martijn van Oosterhout]

Please reply to the list also in the future, I'm going to sleep now.
Anyway, it still works:

kleptog=# create temp table x (a text);
CREATE TABLE
kleptog=# insert into x values (nullif('','')); 
INSERT 114760 1
kleptog=# insert into x values (nullif('1',''));
INSERT 114761 1
kleptog=# select a,a is null as isnull from x;
 a | isnull 
---+
   | t
 1 | f
(2 rows)

Please show us the error you got.

Have a nice day,


On Thu, Aug 25, 2005 at 02:57:29PM -0700, Matt A. wrote:
> hi. I am speaking of INSERT not SELECT
> 
> --- Martijn van Oosterhout  wrote:
> 
> > Very odd, what are you trying?
> > 
> > kleptog=# select nullif('','') is null;
> >  ?column? 
> > --
> >  t
> > (1 row)
> > 
> > kleptog=# select nullif('1','') is null;
> >  ?column? 
> > --
> >  f
> > (1 row)
> > 
> > Works for me.
> > 
> > Have a nice day,
> > 

-- 
Martijn van Oosterhout  http://svana.org/kleptog/
> Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
> tool for doing 5% of the work and then sitting around waiting for someone
> else to do the other 95% so you can sue them.


pgpyRFqMqlNzG.pgp
Description: PGP signature

[GENERAL] POSS. FEATURE REQ: "Dynamic" Views

[CSN]

For lack of a better term, but I was curious if there
is/was any thought about making PG's views
automatically "see" changes in underlying tables, as
opposed to currently having to drop/create all
corresponding views if a table's structure (add/delete
fields, etc.) is changed.

CSN




Start your day with Yahoo! - make it your home page 
http://www.yahoo.com/r/hs 
 

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

[Martijn van Oosterhout]

Very odd, what are you trying?

kleptog=# select nullif('','') is null;
 ?column? 
--
 t
(1 row)

kleptog=# select nullif('1','') is null;
 ?column? 
--
 f
(1 row)

Works for me.

Have a nice day,

On Thu, Aug 25, 2005 at 01:44:52PM -0700, Matt A. wrote:
> We used nullif('$value','') on inserts in mssql.  We
> moved to postgres but the nullif() function doesn't
> match empty strings to each other to return null.
> 
> MS SQL2000 nullif('1','') would insert 1 as integer
> even though wrapped in '' (aka string). Also
> nullif('','') would evaluate NULL (both equal returns
> NULL) and insert the "not a value" accordingly, not
> return text 'NULL' instead of return NULL if '' == ''.
> Postgresql will through an error since the defined
> return type is text. 
> 
> I would like to alter this function all together. How
> could I find it to manipulate it?
> 
> We cannot always enter a value for a integer, text,
> boolean, etc column. 
> 
> Is there any other way to accomplish this in any
> language? C? Python? PL/x? If so can you suggest where
> we could learn how to do this or provide an example? 
> 
> Please help.
> 
> Domo
> Matthew
> 
> 
>   
> 
> Start your day with Yahoo! - make it your home page 
> http://www.yahoo.com/r/hs 
>  
> 
> ---(end of broadcast)---
> TIP 6: explain analyze is your friend

-- 
Martijn van Oosterhout  http://svana.org/kleptog/
> Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
> tool for doing 5% of the work and then sitting around waiting for someone
> else to do the other 95% so you can sue them.


pgpdWXhgNJFv9.pgp
Description: PGP signature

Re: [GENERAL] Postgresql replication

[Chris Browne]

William Yu <[EMAIL PROTECTED]> writes:
> David Goodenough wrote:
>> The most obvious one that does exactly this (generic multi-master
>> replication) is Lotus Domino.  It is not a relational DB, but not 
>> sufficiently
>> far off to stop the analogy.
>> Domino marks each document with a binary value which identifies the
>> server (built from a hash of the server name and the time the DB was
>> created) and a timestamp when it was last modified, and also each document
>> (record) has an ID (like OIDs).  More recent versions also do this at a field
>> level to avoid conflicts and speed replication.  When two servers replicate
>
> This system sounds ok for documents and general data that can always
> be revived via version control/history. But I can't see how this
> would work for financial transactions where you're dealing with
> money and bank accounts. Suppose I have $100 in my account. I
> decided to login to multiple servers and wire transfer $100 to
> another account on every server. And I hit submit exactly at the
> same time for every server so check. Sure they can resolve the
> conflict afterwards in terms of saying in terms of which transfer to
> kill off. But the fact is that my other account has that N x $100
> already and I've just fleeced the bank.

There are two pretty reasonable ways to address this:

1.  Your application does not replicate balances; those are always
computed locally.  Only *transactions* are relayed.

2.  Balance updates take place as a special kind of "delta update"
where replication transfers around (old.balance - new.balance) instead
of either of the values.

Either of these leads to the nifty case where that bank discovers that
you have overdrawn your account, and then takes whatever actions they
deem are appropriate next.

Such as billing you $29 for each overdraft transaction.

You're hardly going to be retiring to the Bahamas on Nx$100, are you
:-).
-- 
select 'cbbrowne' || '@' || 'ntlug.org';
http://cbbrowne.com/info/internet.html
Rules of the Evil Overlord  #211. "If my chief engineer displeases me,
he will be shot, not imprisoned  in the dungeon or beyond the traps he
helped design." 

---(end of broadcast)---
TIP 1: 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] Postgresql replication

[Chris Browne]

[EMAIL PROTECTED] (Steve Atkins) writes:
> On Thu, Aug 25, 2005 at 12:20:52PM -0400, Chris Browne wrote:
>> [EMAIL PROTECTED] (Bohdan Linda) writes:
>> > I would have a slight offtopic question, this is issue only of pgsql or
>> > there are some other db solutions which have good performance when doing
>> > this kind of replication across the world.
>> 
>> Asynchronous multimaster replication is pretty much a generally tough
>> problem.
>> 
>> Oracle, Sybase, and DB2 all have methods of doing it; none are either
>> simple, straightforward, or transparent to use.
>> 
>> It's a tough problem, in general.
>
> I've been involved in one project that did multi-master async replication
> across geographically separated servers.
>
> It worked but was operationally so horrible that I've sworn never to
> architect a system that requires multi-master replication at the
> database ever again. Application level? Possibly. But not at the DB
> level.

I have heard something of the same argument being thrown at attempts
to use things like CORBA to allow you to pretend that there is no
difference between local and remote access to things.

There are some good arguments to be made, there.

You really need to access remote data in different ways than local
data because the latency *will* kill you if you ignore it.

It's a well and neat idea to try to find ways to hide those
differences under the carpet; it is quite likely that the "better way"
will involve addressing that somewhere in the application, as opposed
to trying to get the DB (or the ORB or other 'distributed thingie') to
do it for you.

Tables that contain balances (e.g. - credit balances, account
balances, and such) would be a good example.  You do NOT want to
distribute the process of updating balances across some
hideous-latency "Great Divide" link.  That heads, of course, to
application design, not to "pure DB level" activity...
-- 
(format nil "[EMAIL PROTECTED]" "cbbrowne" "cbbrowne.com")
http://www.ntlug.org/~cbbrowne/advocacy.html
"Tools that are no good require more skill."

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

Re: [GENERAL] getting last day of month

[Chris Browne]

[EMAIL PROTECTED] (Sergey Pariev) writes:
> I need to find out the last day of current month. Currently I do the
> trick with code below, but that's rather ugly way to do it IMHO. Could
> anybody suggest me a better way ?


log_analysis=# select date_trunc('months', (date_trunc('months', now()) + '45 
days'::interval)) - '1 day'::interval;
?column?

 2005-08-31 00:00:00+00
(1 row)

So...

log_analysis=# create or replace function eom (timestamptz) returns timestamptz 
as '
log_analysis'# select date_trunc(''months'', (date_trunc(''months'', $1) + ''45 
days''::interval)) - ''1 day''::interval;' language sql;
CREATE FUNCTION
log_analysis=# select eom(now());
  eom   

 2005-08-31 00:00:00+00
(1 row)
log_analysis=# select eom('2004-07-02');
  eom   

 2004-07-31 00:00:00+00
(1 row)

log_analysis=# select eom('2004-02-29');
  eom   

 2004-02-29 00:00:00+00
(1 row)
log_analysis=# select eom('2004-02-29'), eom('2005-02-28'), eom('2005-03-01');
  eom   |  eom   |  eom   
++
 2004-02-29 00:00:00+00 | 2005-02-28 00:00:00+00 | 2005-03-31 00:00:00+00
(1 row)
-- 
let name="cbbrowne" and tld="cbbrowne.com" in name ^ "@" ^ tld;;
http://www3.sympatico.ca/cbbrowne/emacs.html
Q: How many Newtons does it take to change a light bulb?
A: Faux!  There to eat lemons, axe gravy soup!

---(end of broadcast)---
TIP 1: 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] Postgresql replication

[Chris Browne]

[EMAIL PROTECTED] (Brad Nicholson) writes:
> Bohdan Linda wrote:
>
>>I would have a slight offtopic question, this is issue only of pgsql or
>>there are some other db solutions which have good performance when doing
>>this kind of replication across the world.
>>
>>
>>
> It all depends on the quality of the connection Node A to Node B.  If
> connectivity is poor, then it is impossible to have good performance
> doing anything across that connection.

The "nifty magic" of asynchronous multimaster would be that you'd be 
able to have fast access at "Site Alpha" to "Node Alpha" as well as 
fast access at "Site Beta" to "Node Beta." 
 
That would, indeed, be valuable.  That's why it's one of those "Holy 
Grail" things...  It's Rather Hard, which is why there isn't a 
"Slony-III: The Wrath of Async MultiMaster" just yet. 
 
It would be valuable for someone to look into a replication system to 
support that sort of scenario.  With the stipulation that there be 
some big, fiery dragons there ready to eat you and your data :-). 
-- 
(format nil "[EMAIL PROTECTED]" "cbbrowne" "ntlug.org")
http://www3.sympatico.ca/cbbrowne/linuxdistributions.html
"I'm guilty of a lot of things, but I didn't ever do that.''  
-- Bill Clinton, on why he had astroturf lining the back of his pickup
   truck
[In fact, he _DID_ do this, thus making life creepier than fiction...]

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

   http://archives.postgresql.org

Re: [GENERAL] drop table before create

[Lee Harr]

I have not been able to work out how to do this is Postgres 8
(pseudo-code)

 if exists table foo
   drop table foo;
 end
 create table foo;

If I go with

  drop table foo;
  create table foo;

then it barfs on an empty db.




The assumption here is that the SQL is coming in on a script via the
programmatic interface.  Slurp in a bunch of SQL commands and then fire
them at the database.




There are a couple of commands that may help you:

\set ON_ERROR_STOP
\unset ON_ERROR_STOP

_
Express yourself instantly with MSN Messenger! Download today - it's FREE! 
http://messenger.msn.click-url.com/go/onm00200471ave/direct/01/



---(end of broadcast)---
TIP 1: 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] Odd Problems

[Ralph Mason]

Hi,

I have about 15 postgres databases, recently I have noticed on a few of 
them odd errors. 

Basically I have seen two different manifestations of the same problem 
(which I think is index coruption).


In case 1, updates and or inserts fail due to a constraint on the table, 
however they should be succeeding.


in the second case I get ERROR:  could not access status of transaction 
126746624  and an error following about a log file (in data/pg_clog) 
that does not exisit.


In both case doing a REINDEX on the table fixes the problem.   I have 
had the problem happen about 5 times over the last week on 3 different 
databases on different tables.


Running: PostgreSQL 8.0.1 on i686-pc-linux-gnu, compiled by GCC 
i686-pc-linux-gnu-gcc (GCC) 3.3.5  (Gentoo Linux 3.3.5-r1, ssp-3.3.2-3, 
pie-8.7.7.1)


Thanks
Ralph









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

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

Re: [GENERAL] Postgresql replication

[Jeff Davis]

Carlos Henrique Reimer wrote:
> Exactly
> 

Was there something lacking in my suggested solution at:


It's a little complicated to administer, but it seems well-suited to a
company that has several locations that want to share data without being
too dependent upon eachother.

Regards,
Jeff Davis

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

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

Re: [GENERAL] Postgresql replication

[William Yu]

David Goodenough wrote:

The most obvious one that does exactly this (generic multi-master
replication) is Lotus Domino.  It is not a relational DB, but not sufficiently
far off to stop the analogy.

Domino marks each document with a binary value which identifies the
server (built from a hash of the server name and the time the DB was
created) and a timestamp when it was last modified, and also each document
(record) has an ID (like OIDs).  More recent versions also do this at a field
level to avoid conflicts and speed replication.  When two servers replicate


This system sounds ok for documents and general data that can always be 
revived via version control/history. But I can't see how this would work 
for financial transactions where you're dealing with money and bank 
accounts. Suppose I have $100 in my account. I decided to login to 
multiple servers and wire transfer $100 to another account on every 
server. And I hit submit exactly at the same time for every server so 
check. Sure they can resolve the conflict afterwards in terms of saying 
in terms of which transfer to kill off. But the fact is that my other 
account has that N x $100 already and I've just fleeced the bank.


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

[GENERAL] help

[Matt A.]

We used nullif('$value','') on inserts in mssql.  We
moved to postgres but the nullif() function doesn't
match empty strings to each other to return null.

MS SQL2000 nullif('1','') would insert 1 as integer
even though wrapped in '' (aka string). Also
nullif('','') would evaluate NULL (both equal returns
NULL) and insert the "not a value" accordingly, not
return text 'NULL' instead of return NULL if '' == ''.
Postgresql will through an error since the defined
return type is text. 

I would like to alter this function all together. How
could I find it to manipulate it?

We cannot always enter a value for a integer, text,
boolean, etc column. 

Is there any other way to accomplish this in any
language? C? Python? PL/x? If so can you suggest where
we could learn how to do this or provide an example? 

Please help.

Domo
Matthew




Start your day with Yahoo! - make it your home page 
http://www.yahoo.com/r/hs 
 

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

Re: [GENERAL] Help with a subselect inside a view

[Bruno Wolff III]

On Thu, Aug 25, 2005 at 08:19:25 -0700,
  Bill Moseley <[EMAIL PROTECTED]> wrote:
> 
> DROP VIEW cl;
> CREATE VIEW cl  (id, instructor)
> AS
> SELECT class.id, person.first_name
>   FROM class, instructors, person
>  WHERE instructors.person = person.id
>AND class.id = (
>SELECT instructors.id
>  FROM instructors, person
> WHERE instructors.class = class.id
>   AND person.id = instructors.person
> LIMIT 1
> );
> 
> Which returns a row for every row in "instructors" table.

I think if you were to use this approach you would do something more like:

DROP VIEW cl;
CREATE VIEW cl  (id, instructor)
AS
SELECT class.id,
(SELECT person.first_name
   FROM instructors, person
   WHERE instructors.class = class.id
 AND person.id = instructors.person
   LIMIT 1)
  FROM class;

---(end of broadcast)---
TIP 1: 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] Postgresql replication

[Steve Atkins]

On Thu, Aug 25, 2005 at 12:20:52PM -0400, Chris Browne wrote:

> [EMAIL PROTECTED] (Bohdan Linda) writes:
> > I would have a slight offtopic question, this is issue only of pgsql or
> > there are some other db solutions which have good performance when doing
> > this kind of replication across the world.
> 
> Asynchronous multimaster replication is pretty much a generally tough
> problem.
> 
> Oracle, Sybase, and DB2 all have methods of doing it; none are either
> simple, straightforward, or transparent to use.
> 
> It's a tough problem, in general.

I've been involved in one project that did multi-master async replication
across geographically separated servers.

It worked but was operationally so horrible that I've sworn never to
architect a system that requires multi-master replication at the
database ever again. Application level? Possibly. But not at the DB
level.

Cheers,
  Steve

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

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

Re: [GENERAL] Postgresql replication

[Matt Miller]

> > http://pgfoundry.org/projects/pgcluster/ which provides syncronous
> > multi-master clustering.
>
> He specifically said that pgcluster did not work for him
> because ...PGCluster requires that there be a load balancer and a
> replicator centrally located managing the cluster. If a network
> problem happens at the centralized location, it would bring down all
> locations completely.

I think the load balancer is an optional component.  Clients can connect
either to a load balancer, or directly to a specific machine.

Also, I think pgcluster can operate async.  If the system runs in
"normal" mode then the client gets a response as soon as the transaction
is committed on the local machine.  In "reliable" mode the client waits
for the commit to happen on all machines.

See
http://pgcluster.projects.postgresql.org/structure_of_replication.html

---(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] Postgresql replication

[Carlos Henrique Reimer]

ExactlyJeff Davis <[EMAIL PROTECTED]> escreveu:
Jim C. Nasby wrote:> Or, for something far easier, try> http://pgfoundry.org/projects/pgcluster/ which provides syncronous> multi-master clustering.> He specifically said that pgcluster did not work for him because thedatabases would be at physically seperate locations. PGCluster requiresthat there be a load balancer and a replicator centrally locatedmanaging the cluster. If a network problem happens at the centralizedlocation, it would bring down all locations completely.I think he's looking for an async solution because of that. In mysolution, if one location goes down, the others keep going.Regards,Jeff Davis
		Yahoo! Acesso Grátis: Internet rápida e grátis. Instale o discador agora!

Re: [GENERAL] Postgresql replication

[William Yu]

Tino Wildenhain wrote:
If your app is used by external customers who are all across the 
country, they want to continue to still use your software even though 
you and data center #1 are 6 feet under due to an 8.0 earthquake. They 
want auto-failover to data center #2 which is in close proximity to 
CIA headquarters and other juicy terrorist targets.



Sure, but in this case a "simple" async master-slave (slony-1)
and the usual failover (also DNS-failover) should be sufficient.


Workable if you're willing to keep multiple sets of servers idling on 
stand-by only for catastrophic failure. For temporary failure, I believe 
at this time Slony requires manual admin work to resync a promoted 
master and demote it back to slave. Not a big deal if the downtime 
doesn't happen often but when you're depending a cross-country internet 
connection, it happens far more often than you like to deal with.


Of course, we're also using multi-master replication to load balance 
traffic across multiple servers because the price jump from going 2xDC 
to 4xDC is major. So we have a bunch of 2x1 and 2xDC servers across the 
country serving our customers versus 1 big server for primary access and 
1 big server as a standby.


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

Re: [GENERAL] ctid access is slow

[Vivek Khera]

On Aug 23, 2005, at 10:02 AM, Ilja Golshtein wrote:


The only thing I am curios is ctid good for
anything from user point of view?



I have a very specific use for it -- to bypass the index on an  
update.  Something like this:


select ctid,user_id from users where ...
 ... do stuff based on user_id ...
update users set last_mod=CURRENT_TIME where ctid='$ctid' and user_id= 
$user_id


since I have already locked those rows earlier in the transaction I  
worry not about anyone else updating those rows.  However, the extra  
safetynet of checking that the current row at $ctid is still the one  
I want, I check that.  If the row is not updated (ie, count 0  
returned) then I do a standard update based just on the user_id which  
is the PK.


When you add this up over millions of rows, it makes a difference to  
bypass the PK index lookup every time.


Vivek Khera, Ph.D.
+1-301-869-4449 x806



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

Re: [GENERAL] Postgresql replication

[Jeff Davis]

Jim C. Nasby wrote:
> Or, for something far easier, try
> http://pgfoundry.org/projects/pgcluster/ which provides syncronous
> multi-master clustering.
> 

He specifically said that pgcluster did not work for him because the
databases would be at physically seperate locations. PGCluster requires
that there be a load balancer and a replicator centrally located
managing the cluster. If a network problem happens at the centralized
location, it would bring down all locations completely.

I think he's looking for an async solution because of that. In my
solution, if one location goes down, the others keep going.

Regards,
Jeff Davis

---(end of broadcast)---
TIP 1: 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] Is there such a thing as a 'background database job'?

[Vivek Khera]

On Aug 22, 2005, at 10:53 PM, Mike Nolan wrote:

In a recent discussion with an academician friend of mine regarding  
how
to improve performance on a system, he came up with the idea of  
taking what
is now a monthly purge/cleanup job that takes about 24 hours (and  
growing)

and splitting it up into a series of smaller tasks.



Well, the purge/cleanup requires a finite amount of work X.  Either  
you make that a more efficient process requiring Y < X amount of  
time, or you split up the process so that X/N time is used for each  
of the N runs of the cleanup, or you do both...


Just splitting the job  doesn't mean it will take less time overall,  
since it still must do the same total amount of work.


I'm guessing that when you're cleanup is running, it impacts the  
performance of the rest of the system, and that is the "faster" you  
want to achieve.


My recommendation of what you want to do (and this is what I do) for  
your cleanup process is to throttle it and let it run over many days  
but pause between smaller tasks, or change your procedure to let you  
run your cleanup once per week or per day incrementally.


In my case, I need to purge about 6000 rows from one table, but the  
cascading deletes end up removing anywhere from 500 to 200k rows in  
other tables per row deleted in the main table.  Since I know how  
many referenced rows will be removed, I keep a tally and when the  
total reaches > 150k rows, I pause for a while.  If an individual  
rows results in > 10k related rows being deleted, I pause for a  
smaller amount of time.


This keeps everything moving along, and *nobody* notices.  So what if  
it takes 3 days to finish...



Vivek Khera, Ph.D.
+1-301-869-4449 x806



---(end of broadcast)---
TIP 1: 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] Postgresql replication

[Chris Browne]

[EMAIL PROTECTED] (Bohdan Linda) writes:
> I would have a slight offtopic question, this is issue only of pgsql or
> there are some other db solutions which have good performance when doing
> this kind of replication across the world.

Asynchronous multimaster replication is pretty much a generally tough
problem.

Oracle, Sybase, and DB2 all have methods of doing it; none are either
simple, straightforward, or transparent to use.

It's a tough problem, in general.
-- 
(reverse (concatenate 'string "gro.mca" "@" "enworbbc"))
http://cbbrowne.com/info/rdbms.html
"I think we might have been better off with a slide rule."
-- Zaphod Beeblebrox

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

   http://archives.postgresql.org

Re: [GENERAL] Help with a subselect inside a view

[Tom Lane]

Bill Moseley <[EMAIL PROTECTED]> writes:
> On Thu, Aug 25, 2005 at 12:14:31PM -0400, Tom Lane wrote:
>> It's fairly pointless though, because as the manual notes, you can't get
>> any well-defined behavior without additional ORDER BY columns to
>> prioritize the rows within class.id groups.  As is, you're getting
>> random choices of class_time and first_name within the groups.
>> (Though maybe in this application, you don't care.)

> I'm not sure I follow what you are saying.  I understand that I have
> no control over which "first_name" I end up with (and I don't really
> care), but class_time is a column in the "class" table which I'm using
> DISTINCT ON on, so that should be unique as well.  So I assume you
> meant random choice of first_name, not class_time.

Sorry, I meant that the query by itself doesn't guarantee anything about
which values you will get.  If you know a-priori that there is only one
value of class_time per class id, then of course you don't care which
row it's selected from.  But from the point of view of this query,
you're getting an unspecified one of the possible values.

In most of the applications I've seen for DISTINCT ON, people *do* care.
For instance, if you wanted to further constrain what you were getting,
you might wish that the returned first_name were the alphabetically
first among the class's instructors.  You could get that with
SELECT DISTINCT ON(class.id)
...
ORDER BY class.id, person.first_name;

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

Re: [GENERAL] Postgresql replication

[Chris Browne]

[EMAIL PROTECTED] (David Goodenough) writes:
> On Thursday 25 August 2005 13:03, William Yu wrote:
>> As far as I know, nobody has a generic solution for multi-master
>> replication where servers are not in close proximity. Single master
>> replication? Doable. Application specific conflict resolution? 
>> Doable.  Off the shelf package that somehow knows financial
>> transactions on a server shouldn't be duplicated on another? 
>> Uhh...I'd be wary of trying it out myself.
>
> The most obvious one that does exactly this (generic multi-master
> replication) is Lotus Domino.  It is not a relational DB, but not
> sufficiently far off to stop the analogy.

There's a better known case, and that's PalmOS "PalmSync."

It does fairly much the same thing as Lotus Notes(tm).

In both cases, they are doing something that works reasonably well for
data that looks somewhat like "documents."  Conflict resolution at the
"document" level is something that users can handle pretty well by
hand.

Unfortunately, for data that's more at the "hordes of little facts"
level, conflict resolution is a tough problem :-(.
-- 
let name="cbbrowne" and tld="cbbrowne.com" in String.concat "@" [name;tld];;
http://www.ntlug.org/~cbbrowne/internet.html
Rules of the  Evil Overlord #223. "I will  install a fire extinguisher
in  every room  --  three, if  the  room contains  vital equipment  or
volatile chemicals." 

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

Re: [GENERAL] Postgresql replication

[Chris Browne]

[EMAIL PROTECTED] (Aly Dharshi) writes:
> I know I am wadding into this discussion as an beginner compared to
> the rest who have answered this thread, but doesn't something like
> pgpool provide relief for pseudo-multimaster replication, and what
> about software like sqlrelay wouldn't these suites help to some extent
> ? Looking forward to be enlightened.

pgpool and sqlrelay provide you a loose equivalent to synchronous
replication, as they have to submit the queries to *all* of the nodes.

If you can live with the long latency times that result if the nodes
are widely separated, that's great.

Unfortunately, the reason for people to want *asynchronous*
multimaster replication is that they /cannot/ afford that latency
time.  They want to submit updates only to the local database, and
have the updates head to the other server some time later.

That's why pgpool/sqlrelay aren't an answer.
-- 
output = ("cbbrowne" "@" "acm.org")
http://cbbrowne.com/info/languages.html
Rules of the  Evil Overlord #46. "If an advisor says  to me "My liege,
he  is but  one man.  What can  one man  possibly do?",  I  will reply
"This." and kill the advisor." 

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

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

Re: [GENERAL] Postgresql replication

[Tino Wildenhain]

William Yu schrieb:

Tino Wildenhain wrote:

Which is the problem we face. Great, you've got multiple servers for 
failover. Too bad it doesn't do much good if your building gets hit 
by fire/earthquake/hurricane/etc.




This would remove the application using that data too, or not? ;)



Yes and no. If your DB is an internal app for a company where the users 
are in the same building as the servers, doesn't matter really I guess. 
Meteor hitting the building would kill the users in addition to the 
server so nobody will be calling you to complain about system downtime.


If your app is used by external customers who are all across the 
country, they want to continue to still use your software even though 
you and data center #1 are 6 feet under due to an 8.0 earthquake. They 
want auto-failover to data center #2 which is in close proximity to CIA 
headquarters and other juicy terrorist targets.


Sure, but in this case a "simple" async master-slave (slony-1)
and the usual failover (also DNS-failover) should be sufficient.


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

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

Re: [GENERAL] Help with a subselect inside a view

[Bill Moseley]

On Thu, Aug 25, 2005 at 12:14:31PM -0400, Tom Lane wrote:
> > CREATE VIEW cl  (id, class_time, instructor)
> > AS
> > SELECT DISTINCT ON(class.id)
> >class.id, class.class_time, person.first_name
> >   FROM class, instructors, person
> >  WHERE instructors.person = person.id
> >AND class.id = instructors.class;
> 
> This is allowed because the code automatically adds "ORDER BY class.id"
> within the view (as you would see if you examined the view with \d).

I see that now.  Might be helpful for the docs to say that for folks
like me.


> It's fairly pointless though, because as the manual notes, you can't get
> any well-defined behavior without additional ORDER BY columns to
> prioritize the rows within class.id groups.  As is, you're getting
> random choices of class_time and first_name within the groups.
> (Though maybe in this application, you don't care.)

I'm not sure I follow what you are saying.  I understand that I have
no control over which "first_name" I end up with (and I don't really
care), but class_time is a column in the "class" table which I'm using
DISTINCT ON on, so that should be unique as well.  So I assume you
meant random choice of first_name, not class_time.

Thanks,




-- 
Bill Moseley
[EMAIL PROTECTED]


---(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] Help with a subselect inside a view

[Tom Lane]

Bill Moseley <[EMAIL PROTECTED]> writes:
>> http://www.postgresql.org/docs/current/static/sql-select.html#SQL-DISTINCT

>The DISTINCT ON expression(s) must match the leftmost ORDER BY
>expression(s). The ORDER BY clause will normally contain additional
>expression(s) that determine the desired precedence of rows within
>each DISTINCT ON group.

> I read that and thought it wasn't a drop-in replacement for my code
> due to the leftmost ORDER BY requirement.  But, it seems to work even
> if that requirement is not met.

> CREATE VIEW cl  (id, class_time, instructor)
> AS
> SELECT DISTINCT ON(class.id)
>class.id, class.class_time, person.first_name
>   FROM class, instructors, person
>  WHERE instructors.person = person.id
>AND class.id = instructors.class;

This is allowed because the code automatically adds "ORDER BY class.id"
within the view (as you would see if you examined the view with \d).
It's fairly pointless though, because as the manual notes, you can't get
any well-defined behavior without additional ORDER BY columns to
prioritize the rows within class.id groups.  As is, you're getting
random choices of class_time and first_name within the groups.
(Though maybe in this application, you don't care.)

regards, tom lane

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

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

Re: [GENERAL] Help with a subselect inside a view

[Bill Moseley]

And about being efficient:

On Thu, Aug 25, 2005 at 08:01:26AM -0700, Bill Moseley wrote:
> DROP VIEW cl;
> CREATE VIEW cl  (id, class_time, instructor)
> AS
> SELECT DISTINCT ON(class.id)
>class.id, class.class_time, person.first_name
>   FROM class, instructors, person
>  WHERE instructors.person = person.id
>AND class.id = instructors.class;

And in a case like above, I'm displaying the list a page at a time.
So I first do a count to find total rows and then a select:

select count(*) from cl where class_time >= now();
select * from cl where class_time >= now() LIMIT 20 OFFSET 40;

I looked at the EXPLAIN ANALYZE for both and both do the join, it
seems.  I guess it has to be that way.

So would it be smart to do the initial count on "class" instead
of the view first?

select count(*) from class where class_time >= now();
select * from cl where class_time >= now() LIMIT 20 OFFSET 40;

That is, Postgresql won't figure out that it only need to look at one
table, right?

-- 
Bill Moseley
[EMAIL PROTECTED]


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

Re: [GENERAL] Query results caching?

[Ron Mayer]

Dann Corbit wrote:


If I have a 4 million row table, with long rows and big varchar columns
and I run a query on a column like this:

SELECT * FROM inventory WHERE product LIKE '%Table%'

It isn't going to be fast on any system with any database.


Hypothetically it seems one could theoretically use some sort
of GIST index not unlike the contrib/trigram stuff to speed
up like clauses like that.   If so, I wonder if down the road
that could be a nice competitive advantage over systems with
less flexible index systems.  Is that a possible TODO?

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

Re: [GENERAL] SOCKET Conection on Windwos 2003 vs PostgreSQL 8.0.1

[Magnus Hagander]

>  Hi,
>  
>  I´m Julio and a have a situation running W2003 and DBExpress.
>  
>  I connect to the W2003 with socket (:5432) and, when i save 
> the package on my table, a new postgres.exe process start. 
> Ok, no problems until here. I disconnect from the W2003 and 
> the new proccess just die
>  
>  The problem occurs when the cable connection fault between 
> the two PC´s after the connection started. The new process 
> that have been created on the W2003 (my server) dont die. 
> Just stay there. I have to reboot the PostgreSQL 8.0.1 to 
> clear the process.

The process should die once the TCP timeout has expired. This is IIRC 2 hours 
by default, it could be 4. You can chance this, but only on a per-server basis.

This happens when the connectino just goes away and there is no packet telling 
the server it's closing. There is no way for postgres to know the client went 
away.

//Magnus

---(end of broadcast)---
TIP 1: 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] SOCKET Conection on Windwos 2003 vs PostgreSQL 8.0.1

[Julio Cesar]

 Hi,
 
 I´m Julio and a have a 
situation running W2003 and DBExpress.
 
 I connect to the W2003 with 
socket (:5432) and, when i save the package on my 
table, a new postgres.exe process start. Ok, no problems until 
here. I disconnect from the W2003 and the new proccess just 
die
 
 The problem occurs when the 
cable connection fault between the two PC´s after the connection 
started. The new process that have been created on the W2003 (my 
server) dont die. Just stay there. I have to reboot the PostgreSQL 
8.0.1 to clear the process.
 
 Maybe i´m doing something wrong, but with 
UNIX Server´s i dont have this situation.
 
 If anyone have a idea, please return ... 
I use PostgreSQL for the last 4 year´s and i have to admit that is simply the 
best choice. Work´s fine and is very, very stable! ! 
! 
 
 
 Regard´s,
 
Julio Cesar Ledo Amaral Filho 
 
 

Re: [GENERAL] Help with a subselect inside a view

[Bill Moseley]

On Thu, Aug 25, 2005 at 08:05:36AM -0500, Bruno Wolff III wrote:
> On Wed, Aug 24, 2005 at 23:12:17 -0700,
>   Bill Moseley <[EMAIL PROTECTED]> wrote:
> > I need a little SQL help:
> > 
> > I'm trying to get a subselect working inside a view.
> 
> Unfortunately you didn't show us what you tried. My guess would be that
> you didn't enclose the subselect in parenthesis.

No, it wasn't that.  I just didn't want to look too foolish. ;)

DROP VIEW cl;
CREATE VIEW cl  (id, instructor)
AS
SELECT class.id, person.first_name
  FROM class, instructors, person
 WHERE instructors.person = person.id
   AND class.id = (
   SELECT instructors.id
 FROM instructors, person
WHERE instructors.class = class.id
  AND person.id = instructors.person
LIMIT 1
);

Which returns a row for every row in "instructors" table.


> The distinct on solution that was suggested is probably a better way to
> go anyway.

Turns out it is, Thanks.


-- 
Bill Moseley
[EMAIL PROTECTED]


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

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

Re: [GENERAL] Help with a subselect inside a view

[Bill Moseley]

Hi David,

On Thu, Aug 25, 2005 at 01:22:02AM -0700, David Fetter wrote:
> This sounds like a case for PostgreSQL's nifty DISTINCT ON functionality.
> 
> http://www.postgresql.org/docs/current/static/sql-select.html#SQL-DISTINCT

   The DISTINCT ON expression(s) must match the leftmost ORDER BY
   expression(s). The ORDER BY clause will normally contain additional
   expression(s) that determine the desired precedence of rows within
   each DISTINCT ON group.

I read that and thought it wasn't a drop-in replacement for my code
due to the leftmost ORDER BY requirement.  But, it seems to work even
if that requirement is not met.

Perhaps I not understanding the wording above?  Or is Postgresql
adding in the order automatically?

My original VIEWS with duplicates:

DROP VIEW cl;
CREATE VIEW cl  (id, class_time, instructor)
AS
SELECT class.id, class.class_time, person.first_name
  FROM class, instructors, person
 WHERE instructors.person = person.id
   AND class.id = instructors.class;


 select * from cl where id = 555;
 id  |   class_time   | instructor 
-++
 555 | 2005-09-30 09:00:00-07 | Cheryl
 555 | 2005-09-30 09:00:00-07 | Bob
(2 rows)


And with DISTINCT ON():

DROP VIEW cl;
CREATE VIEW cl  (id, class_time, instructor)
AS
SELECT DISTINCT ON(class.id)
   class.id, class.class_time, person.first_name
  FROM class, instructors, person
 WHERE instructors.person = person.id
   AND class.id = instructors.class;



 select * from cl where id = 555;
 id  |   class_time   | instructor 
-++
 555 | 2005-09-30 09:00:00-07 | Cheryl
(1 row)


Here where the leftmost ORDER BY doesn't match the DISTINCT ON, which I thought
was not possible:


select * from cl where class_time > now() order by instructor limit 3;
 id  |   class_time   | instructor 
-++
 544 | 2005-08-31 09:00:00-07 | Cheryl
 555 | 2005-09-30 09:00:00-07 | Cheryl
 737 | 2005-08-30 09:00:00-07 | Cynthia


-- 
Bill Moseley
[EMAIL PROTECTED]


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

Re: [GENERAL] Postgresql replication

[Brad Nicholson]

Bohdan Linda wrote:


I would have a slight offtopic question, this is issue only of pgsql or
there are some other db solutions which have good performance when doing
this kind of replication across the world.

 

It all depends on the quality of the connection Node A to Node B.  If 
connectivity is poor, then it is impossible to have good performance 
doing anything across that connection.


--
Brad Nicholson  416-673-4106
Database Administrator, Afilias Canada Corp.



---(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] Postgresql replication

[Brad Nicholson]

Chris Travers wrote:


Carlos Henrique Reimer wrote:

I read some documents about replication and realized that if you plan 
on using asynchronous replication, your application should be 
designed from the outset with that in mind because asynchronous 
replication is not something that can be easily “added on” after the 
fact.

Am I right?



Depending on your needs, you may find pgpool and Slony to be a 
workable combination. This is better when you have a lot of reads and 
only occasional writes. This way writes get redirected back to the 
master, and read-only transactions get run on the slaves.



As long as you don't have any functions that write to the db.  pgpool 
could (and likely would) redirect some of these to the subscriber.  
Slony would prevent the data from being written (which  would prevent 
the subscriber from being corrupted).


--
Brad Nicholson  416-673-4106
Database Administrator, Afilias Canada Corp.



---(end of broadcast)---
TIP 1: 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] getting last day of month

[Patrick . FICHE]

You could try :

SELECT int4(EXTRACT( DAYS FROM CURRENT_DATE + '1 month'::interval -
CURRENT_DATE ));


--- 
Patrick Fiche 
email : [EMAIL PROTECTED] 
tel : 01 69 29 36 18 

--- 




-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] Behalf Of Sergey Pariev
Sent: jeudi 25 aout 2005 18:44
To: pgsql-general postgresql.org
Subject: [GENERAL] getting last day of month


Hi all.
I need to find out the last day of current month. Currently I do the 
trick with code below, but that's rather ugly way to do it IMHO. Could 
anybody suggest me a better way ?

The following is my testing procedure :

CREATE or REPLACE FUNCTION test_findout_dates()
RETURNS integer AS $$
DECLARE
begin_date date;
end_date date;
current_month int;
current_year int;
last_day int;
 BEGIN
current_month := extract ( month from now() ) ;
current_year := extract ( year from now() ) ;

begin_date := current_year || '-' || current_month || '-01' ;

last_day := 31;
begin
end_date := (current_year || '-' || current_month || '-'|| 
last_day) :: date;
last_day := 0 ;
exception
when others then
raise notice '31 doesnt cut for month %',current_month ;
end;

if last_day > 0 then
begin
last_day := 30;
end_date := (current_year || '-' || current_month || '-'|| 
last_day) :: date;
last_day := 0 ;
exception
when others then
raise notice '30 doesnt cut for month %',current_month ;
end;
end if;

if last_day > 0 then
begin
last_day := 29;
end_date := (current_year || '-' || current_month || '-'|| 
last_day) :: date;
last_day := 0 ;
exception
when others then
raise notice '29 doesnt cut for month %',current_month ;
end;
end if;

if last_day > 0 then
begin
last_day := 28;
end_date := (current_year || '-' || current_month || '-'|| 
last_day ) :: date;
last_day := 0 ;
exception
when others then
raise notice '28 doesnt cut for month %',current_month ;

end;
end if;

raise notice 'begin date is % ',begin_date;
raise notice 'end date is % ',end_date;

return 1;
 END;
$$ LANGUAGE plpgsql ;

Thans in Advance, Sergey.


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

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

Re: [GENERAL] getting last day of month

[Tino Wildenhain]

Sergey Pariev schrieb:

Hi all.
I need to find out the last day of current month. Currently I do the 
trick with code below, but that's rather ugly way to do it IMHO. Could 
anybody suggest me a better way ?


The following is my testing procedure :

CREATE or REPLACE FUNCTION test_findout_dates()
RETURNS integer AS $$
DECLARE
   begin_date date;
   end_date date;
   current_month int;
   current_year int;
   last_day int;
BEGIN
   current_month := extract ( month from now() ) ;
   current_year := extract ( year from now() ) ;

   begin_date := current_year || '-' || current_month || '-01' ;

   last_day := 31;
   begin
   end_date := (current_year || '-' || current_month || '-'|| 
last_day) :: date;

   last_day := 0 ;
   exception
   when others then
   raise notice '31 doesnt cut for month %',current_month ;
   end;

   if last_day > 0 then
   begin
   last_day := 30;
   end_date := (current_year || '-' || current_month || '-'|| 
last_day) :: date;

   last_day := 0 ;
   exception
   when others then
   raise notice '30 doesnt cut for month %',current_month ;
   end;
   end if;

   if last_day > 0 then
   begin
   last_day := 29;
   end_date := (current_year || '-' || current_month || '-'|| 
last_day) :: date;

   last_day := 0 ;
   exception
   when others then
   raise notice '29 doesnt cut for month %',current_month ;
   end;
   end if;

   if last_day > 0 then
   begin
   last_day := 28;
   end_date := (current_year || '-' || current_month || '-'|| 
last_day ) :: date;

   last_day := 0 ;
   exception
   when others then
   raise notice '28 doesnt cut for month %',current_month ;

   end;
   end if;

   raise notice 'begin date is % ',begin_date;
   raise notice 'end date is % ',end_date;

   return 1;
END;
$$ LANGUAGE plpgsql ;

Thans in Advance, Sergey.


SELECT date_trunc('month',CURRENT_DATE) + interval '1 month' - interval 
'1 day';



HTH
Tino Wildenhain

---(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] getting last day of month

[josue]

Sergey,

Try this one:

CREATE OR REPLACE FUNCTION public.lastdayofmonth(date)
  RETURNS date AS
'

select ((date_trunc(\'month\', $1) + interval \'1 month\') - interval 
\'1 day\')::date;


'
  LANGUAGE 'sql' VOLATILE;


Sergey Pariev wrote:

Hi all.
I need to find out the last day of current month. Currently I do the 
trick with code below, but that's rather ugly way to do it IMHO. Could 
anybody suggest me a better way ?


The following is my testing procedure :

CREATE or REPLACE FUNCTION test_findout_dates()
RETURNS integer AS $$
DECLARE
   begin_date date;
   end_date date;
   current_month int;
   current_year int;
   last_day int;
BEGIN
   current_month := extract ( month from now() ) ;
   current_year := extract ( year from now() ) ;

   begin_date := current_year || '-' || current_month || '-01' ;

   last_day := 31;
   begin
   end_date := (current_year || '-' || current_month || '-'|| 
last_day) :: date;

   last_day := 0 ;
   exception
   when others then
   raise notice '31 doesnt cut for month %',current_month ;
   end;

   if last_day > 0 then
   begin
   last_day := 30;
   end_date := (current_year || '-' || current_month || '-'|| 
last_day) :: date;

   last_day := 0 ;
   exception
   when others then
   raise notice '30 doesnt cut for month %',current_month ;
   end;
   end if;

   if last_day > 0 then
   begin
   last_day := 29;
   end_date := (current_year || '-' || current_month || '-'|| 
last_day) :: date;

   last_day := 0 ;
   exception
   when others then
   raise notice '29 doesnt cut for month %',current_month ;
   end;
   end if;

   if last_day > 0 then
   begin
   last_day := 28;
   end_date := (current_year || '-' || current_month || '-'|| 
last_day ) :: date;

   last_day := 0 ;
   exception
   when others then
   raise notice '28 doesnt cut for month %',current_month ;

   end;
   end if;

   raise notice 'begin date is % ',begin_date;
   raise notice 'end date is % ',end_date;

   return 1;
END;
$$ LANGUAGE plpgsql ;

Thans in Advance, Sergey.


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






--
Sinceramente,
Josué Maldonado.

... "Monogamia: ilusión falaz de establecer relaciones con una pareja a 
la vez."


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

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

Re: [GENERAL] getting last day of month

[Sergey Moiseev]

Sergey Pariev wrote:
> Hi all.
> I need to find out the last day of current month. Currently I do the
> trick with code below, but that's rather ugly way to do it IMHO. Could
> anybody suggest me a better way ?
select '2005-09-01'::date-'1 day'::interval does the trick :)
-- 
Wbr, Sergey Moiseev

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

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

[GENERAL] getting last day of month

[Sergey Pariev]

Hi all.
I need to find out the last day of current month. Currently I do the 
trick with code below, but that's rather ugly way to do it IMHO. Could 
anybody suggest me a better way ?


The following is my testing procedure :

CREATE or REPLACE FUNCTION test_findout_dates()
RETURNS integer AS $$
DECLARE
   begin_date date;
   end_date date;
   current_month int;
   current_year int;
   last_day int;
BEGIN
   current_month := extract ( month from now() ) ;
   current_year := extract ( year from now() ) ;

   begin_date := current_year || '-' || current_month || '-01' ;

   last_day := 31;
   begin
   end_date := (current_year || '-' || current_month || '-'|| 
last_day) :: date;

   last_day := 0 ;
   exception
   when others then
   raise notice '31 doesnt cut for month %',current_month ;
   end;

   if last_day > 0 then
   begin
   last_day := 30;
   end_date := (current_year || '-' || current_month || '-'|| 
last_day) :: date;

   last_day := 0 ;
   exception
   when others then
   raise notice '30 doesnt cut for month %',current_month ;
   end;
   end if;

   if last_day > 0 then
   begin
   last_day := 29;
   end_date := (current_year || '-' || current_month || '-'|| 
last_day) :: date;

   last_day := 0 ;
   exception
   when others then
   raise notice '29 doesnt cut for month %',current_month ;
   end;
   end if;

   if last_day > 0 then
   begin
   last_day := 28;
   end_date := (current_year || '-' || current_month || '-'|| 
last_day ) :: date;

   last_day := 0 ;
   exception
   when others then
   raise notice '28 doesnt cut for month %',current_month ;

   end;
   end if;

   raise notice 'begin date is % ',begin_date;
   raise notice 'end date is % ',end_date;

   return 1;
END;
$$ LANGUAGE plpgsql ;

Thans in Advance, Sergey.


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

Re: [GENERAL] Query results caching?

[Ben-Nes Yonatan]

Dann Corbit wrote:


-Original Message-
From: Ben-Nes Yonatan [mailto:[EMAIL PROTECTED]
Sent: Monday, August 22, 2005 3:28 PM
To: Jim C. Nasby; Sean Davis; Dann Corbit
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Query results caching?

   


On Mon, Aug 22, 2005 at 10:13:49PM +0200, Ben-Nes Yonatan wrote:

 


I think that I was misunderstood, Ill make an example:
Lets say that im making the following query for the first time on
   


the
 


"motorcycles" table which got an index on the "manufacturer" field:

EXPLAIN ANALYZE SELECT manufacturer FROM motorcycles WHERE
manufacturer='suzuki';
... Total runtime: 3139.587 ms

Now im doing the same query again and i get a much faster result
   


(cause
 


of the "caching"): Total runtime: 332.53 ms

After both of those queries I drop the index and query the table
   


again
 


with the exact same query as before and now I receive: Total
   


runtime:
 


216834.871 ms

And for my last check I run the exact same query again (without
   


creating
 


the INDEX back again) and I get quite similar result to my third
   


query:
 


Total runtime: 209218.01 ms


My problem is that (maybe I just dont understand something basic
here...) the last 2 (also the second query but I dont care about
   


that)
 


queries were using the "cache" that was created after the first
   


query
 


(which had an INDEX) so none of them actually showed me what will
   


happen
 


if a client will do such a search (without an INDEX) for the first
   


time.
 


I want to delete that "caching" after I do the first 2 queries so my
next queries will show me "real life results".
   

 


Ok I tried to handle both of your replies cause I got them at 2
   


seperate
 


emails.

Dann Corbit wrote:
> These results are all what I would expect.  When you delete the
   


index,
 


> the query will be forced to do a table scan (to examine every
   


single
 


> record in the table one by one).  If the table is non-trivial it is
> unlikely that either the OS or the database will cache the whole
   


thing
 


> in memory.  However, when you query a small record set, then it is
> likely to be retained in RAM which is literally thousands of times
> faster than disk.

Didnt know that, good to know though doesnt assure me...
What if I drop the INDEX but create a diffrent INDEX which also make
   


the
 


process alot faster then without an INDEX but slower/faster then the
   


one
 


before, will it wont use the former "caching"?
   



You can add several indexes to a single table.
If you do some statistics on the query patterns, you can find what
indexes are needed to make the queries as fast as possible.

 


> If a search is to be made on a frequent basis, you should create an
> index.
> The query results above show you why.

Obvious :)

> Think about this for a minute.  The real life results you want are
> very fast results.  For that reason, you should try to model the
> customer queries as nearly as possible.  If you have a canned
> application like order entry, then the real parameterized query set
> will probably be quite small in real life.  If you are creating a
> server for ad-hoc queries then it will be far more difficult to
   


model
 


> in real life.
>
> What is the real purpose of the application that you are writing?
>
> Will users be using a pre-programmed front end, or will they be
   


typing
 


> in queries free-form for whatever their heart desires?

Ok ill try to describe the system as short & precise as possible (its
also passed midnight here :)).
Each day I receive about 4 million rows of data (products) which I
insert into table1 (after I delete all of the previous data it had),
along it I receive for every row about another 15 keywords which I
insert into table2 (where as in table1 I delete all of the previous
   


data
 


it had also), this process is a fact that I cant change.
   



If the data arrives on a daily basis, and is not updated until the next
day, I suggest creating a lot of indexes, and cluster on the index used
most frequently.

What exactly are the 15 keywords in the second table for?
Are they column names?
Are they categories for the first table?
Why is the second table necessary at all?
 

Now clustering was unknown to me when I received this email from you... 
THANKS!!! I created 4 replicas of my table ordered by the diffrent order 
that I want to allow my users to use, yep its quite alot of GB but I 
dont care about it as long its working fast, and damn its flying! less 
then 100 ms and thats on a weak server which will be replaced soon!.


 


Now the users of the site can search for data from table1 by typing
whichever (and up to 4) words as they want at a text field (search
   


input
 


string) and the server should display the correct results by querying
table1 & join table2 for its keywords.
   



Can you give the exact table definitions for t

[GENERAL] Problem upgrading from 8.0.1 to 8.0.3

[Clodoaldo Pinto]

I was running 8.0.1 and PHP in FC3 with no problems.

Then i upgraded to 8.0.3 using yum and yum installed
postgresql-libs.i386 0:7.4.8-1.FC3.1. (Is it Ok?)

Now i am receving this message from PHP:
PHP Fatal error:  Call to undefined function:  pg_pconnect()

php-pgsql was already installed. I removed it and tried to reinstall
and got this dependencies errors:

# yum install php-pgsql*
Setting up Install Process
Setting up Repos
base  100% |=| 1.1 kB00:00
updates-released  100% |=|  951 B00:00
Reading repository metadata in from local files
base  : ## 2622/2622
updates-re: ## 1041/1041
Parsing package install arguments
Resolving Dependencies
--> Populating transaction set with selected packages. Please wait.
---> Package php-pgsql.i386 0:4.3.11-2.6 set to be updated
--> Running transaction check
--> Processing Dependency: libpq.so.3 for package: php-pgsql
--> Restarting Dependency Resolution with new changes.
--> Populating transaction set with selected packages. Please wait.
---> Package postgresql-libs.i386 0:7.4.8-1.FC3.1 set to be updated
--> Running transaction check
--> Processing Dependency: libpq.so.4 for package: postgresql-python
--> Processing Dependency: libpq.so.4 for package: postgresql-contrib
--> Processing Dependency: postgresql-libs = 8.0.3 for package:
postgresql-devel--> Processing Dependency: libpq.so.4 for package:
postgresql-server
--> Processing Dependency: libpq.so.4 for package: postgresql
--> Finished Dependency Resolution
Error: Missing Dependency: libpq.so.4 is needed by package postgresql-python
Error: Missing Dependency: libpq.so.4 is needed by package postgresql-contrib
Error: Missing Dependency: postgresql-libs = 8.0.3 is needed by
package postgresql-devel
Error: Missing Dependency: libpq.so.4 is needed by package postgresql-server
Error: Missing Dependency: libpq.so.4 is needed by package postgresql

Regards, Clodoaldo Pinto

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

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

Re: [GENERAL] Query results caching?

[Ben-Nes Yonatan]

Jim C. Nasby wrote:


On Tue, Aug 23, 2005 at 12:27:39AM +0200, Ben-Nes Yonatan wrote:
 


Jim C. Nasby wrote:
   


Emptying the cache will not show real-life results. You are always going
to have some stuff cached, even if you get a query for something new. In
this case (since you'll obviously want those indexes there), after some
amount of time you will have most (if not all) of the non-leaf index
pages cached, since they take a fairly small amount of memory and are
frequently accessed. This makes index traversal *much* faster than your
initial case shows, even if you query on something different each time.
Testing with a completely empty cache just isn't that realistic.
 

As far as I understand it at my situation where all of the data is 
deleted and inserted each day from the start (INDEX will get lost with 
it..) & the endless variety of possible keywords search's & the immense 
size of the tables, the following reason wont last.. or am I wrong here?
   



You're wrong - to an extent. Remember that while you're loading all that
data it's also being cached. Now, some of it will probably end up
falling out of the cache as all the data is read in, but you certainly
won't be starting from the clean slate that you're looking for.
 

Ok I guess that if all of you are telling me this over and over then it 
probably got some point in it :), I guess that I'll just see it work by 
time.


Thanks alot again (I really appreciate it),
Ben-Nes Yonatan
Canaan Surfing ltd.
http://www.canaan.net.il


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

Re: [GENERAL] Postgresql replication

[David Goodenough]

On Thursday 25 August 2005 13:03, William Yu wrote:
> As far as I know, nobody has a generic solution for multi-master
> replication where servers are not in close proximity. Single master
> replication? Doable. Application specific conflict resolution? Doable.
> Off the shelf package that somehow knows financial transactions on a
> server shouldn't be duplicated on another? Uhh...I'd be wary of trying
> it out myself.

The most obvious one that does exactly this (generic multi-master
replication) is Lotus Domino.  It is not a relational DB, but not sufficiently
far off to stop the analogy.

Domino marks each document with a binary value which identifies the
server (built from a hash of the server name and the time the DB was
created) and a timestamp when it was last modified, and also each document
(record) has an ID (like OIDs).  More recent versions also do this at a field
level to avoid conflicts and speed replication.  When two servers replicate
they look for all documents modified since the last replication time, and 
compare the list.  Those only modified on one server are copied across
to the other server replacing the old record and carrying the updated on 
server and timestamp with them.  When a document is deleted in Domino it
actually does not dissapear, it is reduced to a deletion stub, and this gets
replicated as it has the same ID as the original record.  Those that have been
modified on both sides are copied to the other DB, but both records remain and
it is left to the user to resolve conflicts.  Field level replication reduces
the need for this considerably.  Periodically the deletion stubs are purged,
once all known replicas have replicated.

Domino has absolutely no concept of a master DB.

Obviously this scheme would be difficult to do on a pure relational system.
But with triggers and a few standard fields it would not be impossible to
do for a limited application set.  How the user would resolve conflicts would
also be application specific I suspect and how one would relate having two
version of a record in the DB then they both have a field which is supposed to
be unique is also a problem that would have to be resolved (Domino does not 
have the concept of unique keys).

David
  
>
> Bohdan Linda wrote:
> > I would have a slight offtopic question, this is issue only of pgsql or
> > there are some other db solutions which have good performance when doing
> > this kind of replication across the world.
> >
> > Regards,
> > Bohdan
> >
> > On Thu, Aug 25, 2005 at 09:01:49AM +0200, William Yu wrote:
> >>It provides pseudo relief if all your servers are in the same building.
> >>Having a front-end pgpool connector pointing to servers across the world
> >>is not workable -- performance ends up being completely decrepit due to
> >>the high latency.
> >>
> >>Which is the problem we face. Great, you've got multiple servers for
> >>failover. Too bad it doesn't do much good if your building gets hit by
> >>fire/earthquake/hurricane/etc.
>
> ---(end of broadcast)---
> TIP 1: 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

---(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] Help with a subselect inside a view

[Bruno Wolff III]

On Wed, Aug 24, 2005 at 23:12:17 -0700,
  Bill Moseley <[EMAIL PROTECTED]> wrote:
> I need a little SQL help:
> 
> I'm trying to get a subselect working inside a view.

Unfortunately you didn't show us what you tried. My guess would be that
you didn't enclose the subselect in parenthesis.

The distinct on solution that was suggested is probably a better way to
go anyway.

---(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] plpgsql return setof integer?

[Christopher Murtagh]

On Wed, 24 Aug 2005, Alvaro Herrera wrote:

On Wed, Nov 12, 2003 at 11:42:56PM -0500, Christopher Murtagh wrote:

 Thanks once again. You've really helped a lot on this. I especially
liked your 'return qq/{"/ . (join qq/","/, @_) . qq/"}/;' code. If you
were in Montreal, I would owe you a dinner or at least a coffee and a
big thanks. If you ever come this way, please be sure to give me a call.


I didn't really expect to remember this two years later :-), but I
was going over my old mail in order to delete the trash that is usually
left behind.  So if you still have that coffee around, there's news
that you'll be soon able to get rid of it: I'll be visiting McGill Uni
or thereabouts, for a Slony-II meeting.  Maybe I can relieve you of it
then ;-)


 Very cool. Please let me know when you'll be by and I'd be happy to show 
you around, and at the very least buy you that coffee. :-)


Cheers,

Chris

--
Christopher Murtagh
Enterprise Systems Administrator
ISR / Web Service Group
McGill University
Montreal, Quebec
Canada

Tel.: (514) 398-3122
Fax:  (514) 398-2017


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

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

Re: [GENERAL] Postgresql replication

[William Yu]

Tino Wildenhain wrote:
Which is the problem we face. Great, you've got multiple servers for 
failover. Too bad it doesn't do much good if your building gets hit 
by fire/earthquake/hurricane/etc.



This would remove the application using that data too, or not? ;)


Yes and no. If your DB is an internal app for a company where the users 
are in the same building as the servers, doesn't matter really I guess. 
Meteor hitting the building would kill the users in addition to the 
server so nobody will be calling you to complain about system downtime.


If your app is used by external customers who are all across the 
country, they want to continue to still use your software even though 
you and data center #1 are 6 feet under due to an 8.0 earthquake. They 
want auto-failover to data center #2 which is in close proximity to CIA 
headquarters and other juicy terrorist targets.


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

Re: [GENERAL] Postgresql replication

[William Yu]

Another tidbit I'd like to add. What has helped a lot in implementing 
high-latency master-master replication writing our software with a 
business process model in mind where data is not posted directly to the 
final tables. Instead, users are generally allowed to enter anything -- 
could be incorrect, incomplete or the user does not have rights -- the 
data is still dumped into "pending" tables for people with rights to 
fix/review/approve later. Only after that process is the data posted to 
the final tables. (Good data entered on the first try still gets pended 
-- validation phase simply assumes the user who entered the data is also 
the one who fixed/reviewed/approved.)


In terms of replication, this model allows for users to enter data on 
any server. The pending records then get replicated to every server. 
Each specific server then looks at it's own dataset of pendings to post 
to final tables. Final data is then replicated back to all the 
participating servers.


There may be a delay for the user if he/she is working on a server that 
doesn't have rights to post his data. However, the pending->post model 
gets users used to the idea of (1) entering all data in large swoop and 
validating/posting it afterwards and (2) data can/will sit in pending 
for a period of time until it is acted upon with somebody/some server 
with the proper authority. Hence users aren't expecting results to pop 
up on the screen the moment they press the submit button.





William Yu wrote:
Yes, it requires a lot foresight to do multi-master replication -- 
especially across high latency connections. I do that now for 2 
different projects. We have servers across the country replicating data 
every X minutes with custom app logic resolves conflicting data.


Allocation of unique IDs that don't collide across servers is a must. 
For 1 project, instead of using numeric IDs, we using CHAR and 
pre-append a unique server code so record #1 on server A is A01 
versus ?x01 on other servers. For the other project, we were too 
far along in development to change all our numerics into chars so we 
wrote custom sequence logic to divide our 10billion ID space into 
1-Xbillion for server 1, X-Ybillion for server 2, etc.


With this step taken, we then had to isolate (1) transactions could run 
on any server w/o issue (where we always take the newest record), (2) 
transactions required an amalgam of all actions and (3) transactions had 
to be limited to "home" servers. Record keeping stuff where we keep a 
running history of all changes fell into the first category. It would 
have been no different than 2 users on the same server updating the same 
object at different times during the day. Updating of summary data fell 
into category #2 and required parsing change history of individual 
elements. Category #3 would be financial transactions requiring strict 
locks were be divided up by client/user space and restricted to the 
user's home server. This case would not allow auto-failover. Instead, it 
would require some prolonged threshold of downtime for a server before 
full financials are allowed on backup servers.


---(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] Postgresql replication

[William Yu]

As far as I know, nobody has a generic solution for multi-master 
replication where servers are not in close proximity. Single master 
replication? Doable. Application specific conflict resolution? Doable. 
Off the shelf package that somehow knows financial transactions on a 
server shouldn't be duplicated on another? Uhh...I'd be wary of trying 
it out myself.



Bohdan Linda wrote:

I would have a slight offtopic question, this is issue only of pgsql or
there are some other db solutions which have good performance when doing
this kind of replication across the world.

Regards,
Bohdan 


On Thu, Aug 25, 2005 at 09:01:49AM +0200, William Yu wrote:

It provides pseudo relief if all your servers are in the same building. 
Having a front-end pgpool connector pointing to servers across the world 
is not workable -- performance ends up being completely decrepit due to 
the high latency.


Which is the problem we face. Great, you've got multiple servers for 
failover. Too bad it doesn't do much good if your building gets hit by 
fire/earthquake/hurricane/etc.


---(end of broadcast)---
TIP 1: 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] Postgresql replication

[Tino Wildenhain]

Bohdan Linda schrieb:

I would have a slight offtopic question, this is issue only of pgsql or
there are some other db solutions which have good performance when doing
this kind of replication across the world.


it depends entirely on your application. There is no "one size
fits all"

For example to have an online backup, WAL archiving to remote
sites is often sufficient.

However you cannot have synchronous multimaster replication
over slow lines and high performance with updates the same
time.

There is always a tradeoff in any (even in high cost
commercial solutions) you have to carefully consider.



Regards,
Bohdan 


On Thu, Aug 25, 2005 at 09:01:49AM +0200, William Yu wrote:

It provides pseudo relief if all your servers are in the same building. 
Having a front-end pgpool connector pointing to servers across the world 
is not workable -- performance ends up being completely decrepit due to 
the high latency.


Which is the problem we face. Great, you've got multiple servers for 
failover. Too bad it doesn't do much good if your building gets hit by 
fire/earthquake/hurricane/etc.


This would remove the application using that data too, or not? ;)


---(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] Postgresql replication

[Bohdan Linda]

I would have a slight offtopic question, this is issue only of pgsql or
there are some other db solutions which have good performance when doing
this kind of replication across the world.

Regards,
Bohdan 

On Thu, Aug 25, 2005 at 09:01:49AM +0200, William Yu wrote:
> It provides pseudo relief if all your servers are in the same building. 
> Having a front-end pgpool connector pointing to servers across the world 
> is not workable -- performance ends up being completely decrepit due to 
> the high latency.
> 
> Which is the problem we face. Great, you've got multiple servers for 
> failover. Too bad it doesn't do much good if your building gets hit by 
> fire/earthquake/hurricane/etc.
> 
> 

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

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

Re: [GENERAL] Help with a subselect inside a view

[David Fetter]

On Wed, Aug 24, 2005 at 11:12:17PM -0700, Bill Moseley wrote:
> I need a little SQL help:
> 
> I'm trying to get a subselect working inside a view.
> 
> I have a table "class" that has related tables (a class has a
> location, a location has an address with columns city, state, zip).
> I want to use a VIEW to display columns related to a given class.
> 
> But a class can also have one or more instructors.  So I have a link
> table:
> 
>   Table "public.instructors"
>  Column |  Type   | Modifiers 
> +-+---
>  person | integer | not null
>  class  | integer | not null
> 
> Foreign-key constraints:
> "$1" FOREIGN KEY (person) REFERENCES person(id)
> "$2" FOREIGN KEY ("class") REFERENCES "class"(id)
> 
> I can do the following, but in the (very rare) case where there may be
> two instructors assigned to the class I will get two rows back.
> 
> CREATE VIEW class_list
> (
> id, name, class_time, location, location_name,
> address, city, state, zip,
> instructor_name
> )
> AS
> SELECT class.id, class.name, class.class_time, class.location,
>  location.name,
>address.id, address.city, address.state, address.zip,
>person.last_name
> 
>   FROM class, location, address,
>instructors, person
> 
>  WHERE class.location   = location.id
>AND location.address = address.id
>AND location.region  = region.id
>-- Not what I want
>AND instructors.person   = person.id
>AND instructors.class= class.id;
> 
> I'm completely happy to just fetch just one of the instructors, and
> don't care which one.  I just need only one row per class.  (I assume
> that's my hint right there.)

This sounds like a case for PostgreSQL's nifty DISTINCT ON functionality.

http://www.postgresql.org/docs/current/static/sql-select.html#SQL-DISTINCT

HTH :)

Cheers,
D
-- 
David Fetter [EMAIL PROTECTED] http://fetter.org/
phone: +1 510 893 6100   mobile: +1 415 235 3778

Remember to vote!

---(end of broadcast)---
TIP 1: 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] Postgresql replication

[William Yu]

It provides pseudo relief if all your servers are in the same building. 
Having a front-end pgpool connector pointing to servers across the world 
is not workable -- performance ends up being completely decrepit due to 
the high latency.


Which is the problem we face. Great, you've got multiple servers for 
failover. Too bad it doesn't do much good if your building gets hit by 
fire/earthquake/hurricane/etc.



Aly Dharshi wrote:
I know I am wadding into this discussion as an beginner compared to the 
rest who have answered this thread, but doesn't something like pgpool 
provide relief for pseudo-multimaster replication, and what about 
software like sqlrelay wouldn't these suites help to some extent ? 
Looking forward to be enlightened.


Cheers,

Aly.

William Yu wrote:


Carlos Henrique Reimer wrote:

I read some documents about replication and realized that if you plan 
on using asynchronous replication, your application should be 
designed from the outset with that in mind because asynchronous 
replication is not something that can be easily “added on” after the 
fact.



Yes, it requires a lot foresight to do multi-master replication -- 
especially across high latency connections. I do that now for 2 
different projects. We have servers across the country replicating 
data every X minutes with custom app logic resolves conflicting data.


Allocation of unique IDs that don't collide across servers is a must. 
For 1 project, instead of using numeric IDs, we using CHAR and 
pre-append a unique server code so record #1 on server A is 
A01 versus ?x01 on other servers. For the other 
project, we were too far along in development to change all our 
numerics into chars so we wrote custom sequence logic to divide our 
10billion ID space into 1-Xbillion for server 1, X-Ybillion for server 
2, etc.


With this step taken, we then had to isolate (1) transactions could 
run on any server w/o issue (where we always take the newest record), 
(2) transactions required an amalgam of all actions and (3) 
transactions had to be limited to "home" servers. Record keeping stuff 
where we keep a running history of all changes fell into the first 
category. It would have been no different than 2 users on the same 
server updating the same object at different times during the day. 
Updating of summary data fell into category #2 and required parsing 
change history of individual elements. Category #3 would be financial 
transactions requiring strict locks were be divided up by client/user 
space and restricted to the user's home server. This case would not 
allow auto-failover. Instead, it would require some prolonged 
threshold of downtime for a server before full financials are allowed 
on backup servers.


---(end of broadcast)---
TIP 1: 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