Re: [HACKERS] invalid tid errors in latest 7.3.4 stable.

[Kevin Brown]

Stephan Szabo wrote:
> The case at hand (with *'s on the ri queries) assuming pk already
> has an id=1 row would be.
> T1: begin;
> T1: set transaction isolation level serializable;
> T1 ... (something that does a select, not necessarily on either pk or fk)
> T2: begin;
> T2: insert into fk values (1);
> T2*:select * from pk where id=1 for update;
> T2: commit;
> T1: delete from pk where id=1;
> T1*:select * from fk where id=1 for update;
> T1: commit;
> 
> If you want to treat the serial execution as T1 followed by T2.  Then
> T2* would have to show no rows for pk and T2 rolls back.
> 
> If you want to treat the order as T2,T1, then T1* would have to see the
> row that T2 inserted and T1 rolls back.
> 
> Right now, you won't get that, you'll get T2* showing 1 row and T1*
> showing 0 rows.

Does it also behave this way *without* any actual foreign key
constraints in place?  In other words, if you perform the RI queries
explicitly?

If so, then the problem is with the serialization code.  Sounds like
that's pretty much what you're saying.


The problem in the scenario you described should be solved if we mark any
rows that are selected with the "for update" option (either implicitly,
as with RI triggers, or explicitly) as having been modified by the
selecting transaction, the equivalent of (in the case of T2*) "update pk
set id=id where id=1" but without firing any of the ON MODIFY triggers.
A rollback would, of course, not have any effect on the data in those
rows since there weren't any real changes.  This "fix" won't work,
of course, if the serialization code is so broken that it doesn't work
properly even in the face of updates (something I'd find hard to believe).


-- 
Kevin Brown   [EMAIL PROTECTED]

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

Re: [HACKERS] invalid tid errors in latest 7.3.4 stable.

[Stephan Szabo]

On Fri, 26 Sep 2003, Kevin Brown wrote:

> Tom Lane wrote:
> > Stephan Szabo <[EMAIL PROTECTED]> writes:
> > > I think theoretically in serializable the cases where the difference
> > > between the snapshot from this statement and the standard snapshot for the
> > > transaction are noticable we probably have a serialization failure
> >
> > Hmm, that is a good point.  It would be cleaner to throw a "can't
> > serialize" failure than have the RI triggers run under a different
> > snapshot.  I am not sure if we can implement that behavior easily,
> > though.  Can you think of a way to detect whether there's an RI conflict
> > against a later-started transaction?
>
> Just some thoughts on this that, of course, could be wrong.  So please
> don't be too hard on me if I'm full of it.  :-)
>
> By "a later-started transaction" I assume you mean a later-started
> transaction that commits before yours does?
...
> But if PG exhibits exactly the same bug this thread refers to regardless
> of whether a row is examined/modified via directly issued SQL or via
> the RI mechanism then the problem lies not within the RI code at all,
> but within the serialization code.

It's actual a different problem from the original one at this point.  If
one just switches to using whatever snapshot is in place for the
transaction, you run into the problem that our serializable isolation mode
isn't entirely serializable and therefore isn't sufficient to guarantee
that the constraint is satisfied.

The case at hand (with *'s on the ri queries) assuming pk already
has an id=1 row would be.
T1: begin;
T1: set transaction isolation level serializable;
T1 ... (something that does a select, not necessarily on either pk or fk)
T2: begin;
T2: insert into fk values (1);
T2*:select * from pk where id=1 for update;
T2: commit;
T1: delete from pk where id=1;
T1*:select * from fk where id=1 for update;
T1: commit;

If you want to treat the serial execution as T1 followed by T2.  Then
T2* would have to show no rows for pk and T2 rolls back.

If you want to treat the order as T2,T1, then T1* would have to see the
row that T2 inserted and T1 rolls back.

Right now, you won't get that, you'll get T2* showing 1 row and T1*
showing 0 rows.

---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

Re: [HACKERS] 2-phase commit

[Tom Lane]

Christopher Kings-Lynne <[EMAIL PROTECTED]> writes:
>> ... You can make this work, but the resource costs
>> are steep.

> So, after 'n' seconds of waiting, we abandon the slave and the slave
> abandons the master.

[itch...]  But you surely cannot guarantee that the slave and the master
time out at exactly the same femtosecond.  What happens when the comm
link comes back online just when one has timed out and the other not?
(Hint: in either order, it ain't good.  Double plus ungood if, say, the
comm link manages to deliver the master's "commit confirm" message a
little bit after the master has timed out and decided to abort after all.)

In my book, timeout-based solutions to this kind of problem are certain
disasters.

regards, tom lane

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

   http://www.postgresql.org/docs/faqs/FAQ.html

Re: [HACKERS] initdb failure (was Re: [GENERAL] sequence's plpgsql)

[Oliver Elphick]

On Fri, 2003-09-26 at 23:18, Bruce Momjian wrote:
> If you are loading from pg_dump, you have lots of copy commands, so how
> do you know which COPY command caused the failure.  You just have the
> line number of _a_ copy.

I would recommend using
  psql -e
so that the sql commands are output too.

Here is a documentation patch:

Index: backup.sgml
===
RCS file: /projects/cvsroot/pgsql-server/doc/src/sgml/backup.sgml,v
retrieving revision 2.30
diff -c -r2.30 backup.sgml
*** backup.sgml 31 Aug 2003 17:32:18 -  2.30
--- backup.sgml 27 Sep 2003 05:45:04 -
***
*** 100,106 
  be read in by the psql program. The
  general command form to restore a dump is
  
! psql dbname < infile
  
  where infile is what
  you used as outfile
--- 100,106 
  be read in by the psql program. The
  general command form to restore a dump is
  
! psql -f infile -d dbname 
  
  where infile is what
  you used as outfile
***
*** 112,117 
--- 112,124 
  psql supports similar options to pg_dump 
  for controlling the database server location and the user name. See
  its reference page for more information.
+
+ 
+
+ With a large dump, it may be difficult to identify where any errors are
+ occurring.  You may use the -e option to psql to print the SQL commands
+ as they are run, so that it is easy to see precisely which commands are
+ causing errors.
 
  
 

-- 
Oliver Elphick[EMAIL PROTECTED]
Isle of Wight, UK http://www.lfix.co.uk/oliver
GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839  932A 614D 4C34 3E1D 0C1C
 
 "My brethren, count it all joy when ye fall into
  various trials, Knowing that the testing of your faith
  produces endurance."  James 1:2,3 


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

Re: [HACKERS] 2-phase commit

[Christopher Kings-Lynne]

> Not "it can", but "it has to".  The master *must* keep hold of that
> request forever (or until the slave responds, or until we reconfigure
> the system not to consider that slave valid anymore).  Similarly, the
> slave cannot forget the maybe-committed transaction on pain of not being
> a valid slave anymore.  You can make this work, but the resource costs
> are steep.  For instance, in Postgres, you don't get to truncate the WAL
> log, for what could be a really really long time --- more disk space
> than you wanted to spend on WAL anyway.  The locks held by the
> maybe-committed transaction are another potentially unpleasant problem;
> you can't release them, no matter what else they are blocking.

So, after 'n' seconds of waiting, we abandon the slave and the slave
abandons the master.

Such a condition is probably a fairly serious failure anyway, and
something that an admin would need to expect.  The admin would also need
to expect to allocate a heap of disk space for WAL.

Chris



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

   http://archives.postgresql.org

Re: [HACKERS] Threads vs Processes

[Christopher Kings-Lynne]

> > We really don't need threads to replace existing functionality. That
> > would be dog work.
>
> No, that's not the point at all.  The problem we are facing at the
> moment with the Windows port is lack of fork(), which means there's
> no way for separate-subprocess backends to inherit variable values
> from the postmaster.  Bruce has been trying to fix that by having the
> subprocesses somehow reload or re-deduce all those variables; which
> is messy, bug-prone, and probably race-condition-prone too.  In a
> threaded implementation it would maybe be relatively easy to initialize
> a new thread's TLS by copying the postmaster thread's TLS, in which case
> a whole pile of as-yet-unwritten Windows-only code won't be needed.

Kepp in mind though all the cool things that could be done if we had
thread capabilities.  eg. evaluating different subexpressings on
fdifferent cpuis for the one query, etc.

Chris



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

Re: [HACKERS] pg_get_triggerdef pretty printing

[Christopher Kings-Lynne]

Ah, I was just thinking that you could do a nice multi-line version (same
as how they are dumped from 7.3.x)

But yes, it doesn't really matter.

Chris

On Fri, 26 Sep 2003, Andreas Pflug wrote:

> Christopher Kings-Lynne wrote:
>
> > I note there is no pretty printing option for pg_get_triggerdef...
>
> Right.
> There's no expression tree displayed, which would make the pretty print
> option necessary.
>
> As long as we don't have reengineering functions for *all* objects, it
> doesn't make sense to implement pretty-print for line formatting on this
> single function.
>
> Regards,
> Andreas
>


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

Re: [HACKERS] Use of $(THREAD_CFLAGS) in CPPFLAGS

[Bruce Momjian]

Tom Lane wrote:
> Bruce Momjian <[EMAIL PROTECTED]> writes:
> > Actually, the most common THREAD_CFLAGS is -pthread, used by Solaris,
> > OSF, and FreeBSD 4.X.  Only Linux uses -D flags for threading.
> 
> So is -pthread a compile-time or link-time flag?
> 
> If we need both THREAD_CPPFLAGS and THREAD_CFLAGS then let's go ahead
> and invent both.

I just did a 'man gcc' on our PostgreSQL FreeBSD server and read:

   -pthread
  Link a user-threaded process against libc_r instead of libc. Ob-
  jects linked into user-threaded  processes  should  be  compiled
  with -D_THREAD_SAFE.

so it seems it is a link flag, and they have a compile flag no one told
me about.  I already have it configured as a compile flag.  Let me fix
that.  I am working on the thread test program so I will commit it all
at once.

-- 
  Bruce Momjian|  http://candle.pha.pa.us
  [EMAIL PROTECTED]   |  (610) 359-1001
  +  If your life is a hard drive, |  13 Roberts Road
  +  Christ can be your backup.|  Newtown Square, Pennsylvania 19073

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

   http://www.postgresql.org/docs/faqs/FAQ.html

Re: [HACKERS] Problem with function permission test in a view

[Tom Lane]

Bruce Momjian <[EMAIL PROTECTED]> writes:
> I am documenting this behavior in the CREATE VIEW manual page, diff
> attached.

> +
> + While access to tables in the view is controlled entirely by permissions
> + on the view, functions called by the view are checked independently.
> +

That seems a tad vague, not to say content-free.  Perhaps instead say
"Access to tables referenced in the view is determined by permissions of
the view owner.  However, functions called in the view are treated the
same as if they had been called directly from the query using the view.
Therefore the user of a view must have permissions to call all functions
used by the view."

As I said earlier, it's quite possible that we should consider this a
mistake.  But it's way too late to consider fixing it for 7.4, even if
we had consensus that it should be changed, which I don't think we do
yet.  In the meantime we should document the behavior clearly.

regards, tom lane

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

Re: [HACKERS] Use of $(THREAD_CFLAGS) in CPPFLAGS

[Tom Lane]

Bruce Momjian <[EMAIL PROTECTED]> writes:
> Actually, the most common THREAD_CFLAGS is -pthread, used by Solaris,
> OSF, and FreeBSD 4.X.  Only Linux uses -D flags for threading.

So is -pthread a compile-time or link-time flag?

If we need both THREAD_CPPFLAGS and THREAD_CFLAGS then let's go ahead
and invent both.

regards, tom lane

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

Re: [HACKERS] Use of $(THREAD_CFLAGS) in CPPFLAGS

[Bruce Momjian]

Tom Lane wrote:
> Bruce Momjian <[EMAIL PROTECTED]> writes:
> > I just noticed that libpq/ecpg use $(THREAD_CFLAGS) as part of CPPFLAGS.
> > Is that correct?  Should it be added to CFLAGS instead?
> 
> I was just noticing that today.  THREAD_CFLAGS typically includes -D
> flags, and as such it *must* be added to CPPFLAGS --- it'd be incorrect
> to add it to CFLAGS.  Possibly we should rename THREAD_CFLAGS to 
> THREAD_CPPFLAGS.  I'm not sure that we have any platforms for which it
> would be useful to invent both THREAD_CPPFLAGS and THREAD_CFLAGS.

Actually, the most common THREAD_CFLAGS is -pthread, used by Solaris,
OSF, and FreeBSD 4.X.  Only Linux uses -D flags for threading.

-- 
  Bruce Momjian|  http://candle.pha.pa.us
  [EMAIL PROTECTED]   |  (610) 359-1001
  +  If your life is a hard drive, |  13 Roberts Road
  +  Christ can be your backup.|  Newtown Square, Pennsylvania 19073

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

   http://www.postgresql.org/docs/faqs/FAQ.html

Re: [HACKERS] Use of $(THREAD_CFLAGS) in CPPFLAGS

[Tom Lane]

Bruce Momjian <[EMAIL PROTECTED]> writes:
> I just noticed that libpq/ecpg use $(THREAD_CFLAGS) as part of CPPFLAGS.
> Is that correct?  Should it be added to CFLAGS instead?

I was just noticing that today.  THREAD_CFLAGS typically includes -D
flags, and as such it *must* be added to CPPFLAGS --- it'd be incorrect
to add it to CFLAGS.  Possibly we should rename THREAD_CFLAGS to 
THREAD_CPPFLAGS.  I'm not sure that we have any platforms for which it
would be useful to invent both THREAD_CPPFLAGS and THREAD_CFLAGS.

regards, tom lane

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

Re: [HACKERS] invalid tid errors in latest 7.3.4 stable.

[Kevin Brown]

Tom Lane wrote:
> Stephan Szabo <[EMAIL PROTECTED]> writes:
> > I think theoretically in serializable the cases where the difference
> > between the snapshot from this statement and the standard snapshot for the
> > transaction are noticable we probably have a serialization failure
> 
> Hmm, that is a good point.  It would be cleaner to throw a "can't
> serialize" failure than have the RI triggers run under a different
> snapshot.  I am not sure if we can implement that behavior easily,
> though.  Can you think of a way to detect whether there's an RI conflict
> against a later-started transaction?

Just some thoughts on this that, of course, could be wrong.  So please
don't be too hard on me if I'm full of it.  :-)

By "a later-started transaction" I assume you mean a later-started
transaction that commits before yours does?

I don't see how RI is any different than dealing with straight SQL
in this regard.  The effect of RI is to read/write/delete rows from a
related table that you otherwise wouldn't read or modify, and that means
that the RI mechanism needs to be treated in exactly the same way that
the equivalent SELECT/UPDATE/DELETE would be.

So the question I have is: what would PG do in the case that you SELECT
the same row(s) that the RI triggers are reading implicitly?  For
instance, suppose we have two tables:

CREATE TABLE corps (id integer PRIMARY KEY, name varchar(32));
CREATE TABLE widgets (id integer PRIMARY KEY, name varchar(32),
corpid integer REFERENCES corps(id) ON DELETE CASCADE);

When, within a transaction, I do:

INSERT INTO widgets (id, name, corpid) VALUES (1, 'cpu', 3);

the RI mechanism will automatically check to make sure that the value
3 is in the id column of the corps table.  Put another way, it will do
an implicit "SELECT id FROM corps WHERE id = 3", right?  So suppose
that for the purposes of testing the serialization code I remove the RI
triggers and then actually do the following:

SELECT id FROM corps WHERE id = 3;
INSERT INTO widgets (id, name, corpid) VALUES (1, 'cpu', 3);

If my transaction is serializable then clearly, when another transaction
does

UPDATE corps SET id = 4 WHERE id = 3;

and commits before my transaction commits, either the updating
transaction is in violation of serializability rules or the inserting
transaction is.  Serialization is maintained if either of those
transactions aborts with a serialization error.

But note that whether or not RI is involved should be entirely
irrelevant.  What matters is what rows each transacion sees and
modifies.  How the row gets looked at doesn't matter; the only thing
that matters is that the row *does* get looked at.

The important thing here is that the effect of the RI mechanism MUST be
the same as if the equivalent manual SQL statements were exected within
the same transaction.  If it's not, then the RI mechanism is broken and
needs to be fixed at that level.

But if PG exhibits exactly the same bug this thread refers to regardless
of whether a row is examined/modified via directly issued SQL or via
the RI mechanism then the problem lies not within the RI code at all,
but within the serialization code.


I just hope I'm not merely stating the obvious here...


-- 
Kevin Brown   [EMAIL PROTECTED]

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

Re: [HACKERS] Separate shared_buffer management process

[Tom Lane]

Bruce Momjian <[EMAIL PROTECTED]> writes:
> Would it be a good idea to have a separate shared buffer process to
> manage the cache?  Could such a process take workload off of the main
> backend and improve their performance?

> Just an idea?

I can't recall if this has been discussed on the list, but I know I've
thought about the idea of a background "buffer writer" process that
would simply cycle through the buffer cache and write out dirty buffers
in some low-priority fashion.

The idea is this would reduce the I/O crunch at checkpoint times, as
well as reducing the odds that any foreground backend process would have
to block waiting for I/O before it could recycle a buffer slot to read
in a page it needs.  (Perhaps the background writer could be tuned to
preferentially write dirty buffers that are near the tail of the LRU
queue, and thus are likely to get recycled soon.)

In the WAL world, you cannot "write a dirty buffer" until you have
written *and synced* the WAL log as least as far as the LSN of the
buffer you want to write.  So a background buffer writer would have
to write WAL buffers as well, and in that context it could find itself
blocking foreground processes.  I'm not sure what this does to the
notion of "background I/O".  Maybe only buffers whose changes are
already synced in WAL should be eligible for background write.
It needs some thought anyway.

regards, tom lane

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

[HACKERS] Separate shared_buffer management process

[Bruce Momjian]

Would it be a good idea to have a separate shared buffer process to
manage the cache?  Could such a process take workload off of the main
backend and improve their performance?

Just an idea?

-- 
  Bruce Momjian|  http://candle.pha.pa.us
  [EMAIL PROTECTED]   |  (610) 359-1001
  +  If your life is a hard drive, |  13 Roberts Road
  +  Christ can be your backup.|  Newtown Square, Pennsylvania 19073

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

[HACKERS] Use of $(THREAD_CFLAGS) in CPPFLAGS

[Bruce Momjian]

I just noticed that libpq/ecpg use $(THREAD_CFLAGS) as part of CPPFLAGS.
(I added that code.)

Is that correct?  Should it be added to CFLAGS instead?

-- 
  Bruce Momjian|  http://candle.pha.pa.us
  [EMAIL PROTECTED]   |  (610) 359-1001
  +  If your life is a hard drive, |  13 Roberts Road
  +  Christ can be your backup.|  Newtown Square, Pennsylvania 19073

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

Re: [HACKERS] initdb failure (was Re: [GENERAL] sequence's plpgsql)

[Bruce Momjian]

Jon Jensen wrote:
> On Fri, 26 Sep 2003, Tom Lane wrote:
> 
> > I was chewing this over with Bruce on the phone just now, and we refined
> > the idea a little.  Some errors (primarily those detected inside the
> > datatype input procedures) can be clearly traced to a specific column,
> > whereas others (such as too many fields on an input line) really can't
> > be nailed down more tightly than a line.  So we were thinking about two
> > different flavors of context info:
> > 
> > CONTEXT:  COPY tablename, line n, field colname: "column data"
> > 
> > versus
> > 
> > CONTEXT:  COPY tablename, line n: "line data"
> 
> Those would be very helpful bits of information.
> 
> > where in each case the data display would be truncated at 100 characters
> > or so (and would have to be omitted in the COPY BINARY case anyway).
> > 
> > If you're really concerned about funny characters messing up the report,
> > we could imagine replacing them by backslash sequences or some such, but
> > I suspect that would create more confusion than it would solve.
> 
> I hate to mention it, but would it be useful/non-overkill to make either
> of those things (context message maximum length and funny character
> escaping) configurable somehow?

Overkill.  Sorry.

-- 
  Bruce Momjian|  http://candle.pha.pa.us
  [EMAIL PROTECTED]   |  (610) 359-1001
  +  If your life is a hard drive, |  13 Roberts Road
  +  Christ can be your backup.|  Newtown Square, Pennsylvania 19073

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

   http://archives.postgresql.org

Re: [HACKERS] initdb failure (was Re: [GENERAL] sequence's plpgsql)

[Jon Jensen]

On Fri, 26 Sep 2003, Tom Lane wrote:

> I was chewing this over with Bruce on the phone just now, and we refined
> the idea a little.  Some errors (primarily those detected inside the
> datatype input procedures) can be clearly traced to a specific column,
> whereas others (such as too many fields on an input line) really can't
> be nailed down more tightly than a line.  So we were thinking about two
> different flavors of context info:
> 
>   CONTEXT:  COPY tablename, line n, field colname: "column data"
> 
> versus
> 
>   CONTEXT:  COPY tablename, line n: "line data"

Those would be very helpful bits of information.

> where in each case the data display would be truncated at 100 characters
> or so (and would have to be omitted in the COPY BINARY case anyway).
> 
> If you're really concerned about funny characters messing up the report,
> we could imagine replacing them by backslash sequences or some such, but
> I suspect that would create more confusion than it would solve.

I hate to mention it, but would it be useful/non-overkill to make either
of those things (context message maximum length and funny character
escaping) configurable somehow?

Jon

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

Re: [HACKERS] [JDBC] initdb failure (was Re: [GENERAL] sequence's plpgsql)

[Kris Jurka]

On Fri, 26 Sep 2003, Tom Lane wrote:

> Bruce Momjian <[EMAIL PROTECTED]> writes:
> > Perhaps we can throw a warning rather than an error, and adjust initdb
> > to be consistent.
>
> I like the idea of reducing the newline consistency check to a warning.
> There is one thing we'd have to watch for though (it's already an issue
> but would become a bigger one): client-side COPY code had better be
> prepared to absorb backend Notice messages while processing COPY IN.
> Currently libpq doesn't read input data at all during a COPY IN loop,
> which means that if the COPY generates more than a few K of warning
> messages, the backend gets blocked on a full pipe and the whole
> operation locks up.  I have been meaning to fix that in libpq anyway,
> but what other client libraries might have the same issue?  Anyone know
> whether JDBC would need a similar fix?
>

JDBC does not support the COPY protocol at the moment.

Kris Jurka


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

   http://archives.postgresql.org

Re: [HACKERS] Problem with function permission test in a view

[Bruce Momjian]

I am documenting this behavior in the CREATE VIEW manual page, diff
attached.


---

Gaetano Mendola wrote:
> "Bruce Momjian" <[EMAIL PROTECTED]> wrote:
> > Tom Lane wrote:
> > > Bruce Momjian <[EMAIL PROTECTED]> writes:
> > > > Someone asked me a question about view and function permissions.  I
> > > > assumed all object access done by a view would be based on the
> > > > permissions on the view, and not the permissions of the objects.
> > >
> > > Table references are checked according to the owner of the view, but use
> > > in a view does not change the execution context for function or operator
> > > calls.  This is how it's always been done.
> > >
> > > > Is this a bug?
> > >
> > > Changing it would be a major definitional change (and a pretty major
> > > implementation change too).  It might be better, but please don't
> > > pre-judge the issue by labeling it a bug.
> >
> > Well, it sure sounds like a bug.  What logic is there that table access
> > use the view permissions, but not function access?  Could we just use
> > SECURITY DEFINER for function calls in views?
> 
> I already had this problem, look here:
> 
> http://groups.google.it/groups?q=postgres+security+definer+gaetano+mendola&hl=en&lr=&ie=UTF-8&oe=UTF-8&selm=b711hu%241g25%241%40news.hub.org&rnum=1
> 
> and I had no reply :-(
> 
> Regards
> Gaetano Mendola
> 
> 
> ---(end of broadcast)---
> TIP 2: you can get off all lists at once with the unregister command
> (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
> 

-- 
  Bruce Momjian|  http://candle.pha.pa.us
  [EMAIL PROTECTED]   |  (610) 359-1001
  +  If your life is a hard drive, |  13 Roberts Road
  +  Christ can be your backup.|  Newtown Square, Pennsylvania 19073
Index: doc/src/sgml/ref/create_view.sgml
===
RCS file: /cvsroot/pgsql-server/doc/src/sgml/ref/create_view.sgml,v
retrieving revision 1.24
diff -c -c -r1.24 create_view.sgml
*** doc/src/sgml/ref/create_view.sgml   12 Sep 2003 00:12:47 -  1.24
--- doc/src/sgml/ref/create_view.sgml   26 Sep 2003 23:53:34 -
***
*** 118,123 
--- 118,129 
  CREATE VIEW vista AS SELECT text 'Hello World' AS hello;
  
 
+ 
+
+ While access to tables in the view is controlled entirely by permissions
+ on the view, functions called by the view are checked independently.
+
+ 
   
  
   

---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

Re: [HACKERS] [GENERAL] Can't Build 7.3.4 on OS X

[Tom Lane]

Peter Eisentraut <[EMAIL PROTECTED]> writes:
>> Peter, do you remember that?

> BE_DLLLIBS; see Makefile.cygwin for example.  (AIX has a similar
> requirement, but handles it differently for bizarre reasons.)

Right, thanks.

> Personally, I think the two-level namespace feature is the opposite of
> useful and we should stick with -flat_namespace, but I might have to give
> in in the interest of having PostgreSQL behave like other packages on that
> system.

What do you have against the two-level namespace stuff?  I find the
arguments at
http://developer.apple.com/documentation/ReleaseNotes/DeveloperTools/TwoLevelNamespaces.html
moderately convincing.  It doesn't seem like linking a symbol from
library A instead of the expected library B would ever be a good idea.
And I read the document as warning that Apple isn't promising there will
never be name conflicts across standard libraries.

It is kind of a PITA to have to be careful to link a shlib against
everything it will use at runtime, but since we have some other
supported platforms where that's required anyway, we don't really have
a choice about maintaining the code to do it.  Given that, I'm actually
kind of attracted to converting the Darwin port to become a platform
where that's required, because Darwin is a platform that I have easy
access to and test on fairly regularly (when my laptop is working,
anyway ;-)).  We'd be less likely to suffer bit rot in this respect
if the Darwin port required it too.

regards, tom lane

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

   http://www.postgresql.org/docs/faqs/FAQ.html

Re: [HACKERS] plpgsql doesn't coerce boolean expressions to boolean

[Tom Lane]

Bruce Momjian <[EMAIL PROTECTED]> writes:
> Where are we on this --- we all decided on #4.  Does this just require
> an announcment in the release notes.

I haven't done anything about it --- been busy with other stuff, and I
wasn't sure we'd agreed to change it for 7.4 anyway.  I'm willing to
make the code change though.

regards, tom lane

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

Re: [HACKERS] plpgsql doesn't coerce boolean expressions to boolean

[Bruce Momjian]

Where are we on this --- we all decided on #4.  Does this just require
an announcment in the release notes.

(I need to complete the release notes soon.)

---

Tom Lane wrote:
> Following up this gripe
> http://archives.postgresql.org/pgsql-sql/2003-09/msg00044.php
> I've realized that plpgsql just assumes that the test expression
> of an IF, WHILE, or EXIT statement is a boolean expression.  It
> doesn't take any measures to ensure this is the case or convert
> the value if it's not the case.  This seems pretty bogus to me.
> 
> However ... with the code as it stands, for pass-by-reference datatypes
> any nonnull value will appear TRUE, while for pass-by-value datatypes
> any nonzero value will appear TRUE.  I fear that people may actually be
> depending on these behaviors, particularly the latter one which is
> pretty reasonable if you're accustomed to C.  So while I'd like to throw
> an error if the argument isn't boolean, I'm afraid of breaking people's
> function definitions.
> 
> Here are some possible responses, roughly in order of difficulty
> to implement:
> 
> 1. Leave well enough alone (and perhaps document the behavior).
> 
> 2. Throw an error if the expression doesn't return boolean.
> 
> 3. Try to convert nonbooleans to boolean using plpgsql's usual method
>for cross-type coercion, ie run the type's output proc to get a
>string and feed it to bool's input proc.  (This seems unlikely to
>avoid throwing an error in very many cases, but it'd be the most
>consistent with other parts of plpgsql.)
> 
> 4. Use the parser's coerce_to_boolean procedure, so that nonbooleans
>will be accepted in exactly the same cases where they'd be accepted
>in a boolean-requiring SQL construct (such as CASE).  (By default,
>none are, so this isn't really different from #2.  But people could
>create casts to boolean to override this behavior in a controlled
>fashion.)
> 
> Any opinions about what to do?
> 
>   regards, tom lane
> 
> ---(end of broadcast)---
> TIP 2: you can get off all lists at once with the unregister command
> (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
> 

-- 
  Bruce Momjian|  http://candle.pha.pa.us
  [EMAIL PROTECTED]   |  (610) 359-1001
  +  If your life is a hard drive, |  13 Roberts Road
  +  Christ can be your backup.|  Newtown Square, Pennsylvania 19073

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

Re: [HACKERS] initdb failure (was Re: [GENERAL] sequence's plpgsql)

[Bruce Momjian]

Peter Eisentraut wrote:
> Tom Lane writes:
> 
> > so it appears that cygwin's "echo" generates a different newline style
> > than what got put into sql_features.txt.  A possible way to fix this is
> > to put the "\." line into sql_features.txt, but maybe there's a cleaner
> > answer.  Peter, any thoughts?
> 
> There's no clean answer to this on Cygwin.  This specific case is just a
> little problem that we could solve locally, but in general you'll just end
> up annoying people if you require them to use consistent line endings on
> Cygwin.

At this point, we have one mixed-EOL case (initdb, fixable), and one \r
in data case (from 7.2).  Seems the safest solution is to fix initdb and
see what other failure reports we get.

If we downgrade it to a warning, we will not know about the failures;
someone could miss a warning in a dump, but probably will not mess an
error and an empty table.  If we get more reports like initdb, we can
reevaluate, but it seems the safest course is to keep our existing code,
which only removes carriage returns when it quite sure.

-- 
  Bruce Momjian|  http://candle.pha.pa.us
  [EMAIL PROTECTED]   |  (610) 359-1001
  +  If your life is a hard drive, |  13 Roberts Road
  +  Christ can be your backup.|  Newtown Square, Pennsylvania 19073

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

Re: [HACKERS] 2-phase commit

[Gavin Sherry]

On Fri, 26 Sep 2003, Christopher Browne wrote:

> [EMAIL PROTECTED] (Bruce Momjian) writes:
> > Patrick Welche wrote:
> >> On Fri, Sep 26, 2003 at 02:49:30PM -0300, Marc G. Fournier wrote:
> >> ...
> >> > if we are talking two computers sitting next to each other on a switch,
> >> > you'd expect those to be low ... but if you were talking about two
> >> > seperate geographical locations (and yes, I realize you are adding lag to
> >> > the mix with waiting for responses), you'd expect those #s to rise ...
> >>
> >> Which I thought was the whole point of using a group communication
> >> protocol such as spread in postgresql-r. It seemed solved there...
> >
> > Right, but I think we want to try to do two-phase commit without
> > spread.  Spread seems overkill for this usage.
>
> Is there some big demerit to _having_ that "overkill"?  If there is no
> major price to pay, then I don't see why it isn't reasonable to simply
> say "Sure, we'll use that!"

I recall Darren Johnson (who is working on replication with spread) saying
that it required a lot of bandwidth in real world scenarios.

Gavin

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

Re: [HACKERS] 2-phase commit

[Mike Mascari]

Marc G. Fournier wrote:

> On Fri, 26 Sep 2003, Tom Lane wrote:
> 
>>Bruce Momjian <[EMAIL PROTECTED]> writes:
>>
>>>Tom Lane wrote:
>>>
You're not considering the possibility of a transient communication
failure.
>>
>>>Can't the master re-send the request after a timeout?
>>
>>Not "it can", but "it has to".  The master *must* keep hold of that
>>request forever (or until the slave responds, or until we reconfigure
>>the system not to consider that slave valid anymore).  Similarly, the
>>slave cannot forget the maybe-committed transaction on pain of not being
>>a valid slave anymore.
> 
> Hr ... is there no way of having part of the protocol being a message
> sent back that its a valid/invalid slave?  ie. slave has an uncommitted
> transaction, never hears back from master to actually do the commit, so
> after x-secs * y-retries any messages it does try to send to the master
> have a bit flag set to 'invalid'?

If I understand Andrew Sullivan's request, the purpose for integration
of 2-PC into PostgreSQL, is more for distributed query than
replication via an XA interface:

http://sybooks.sybase.com/onlinebooks/group-xsarc/xsge/xatuxedo/@ebt-link;pt=61?target=%25N%13_446_START_RESTART_N%25

If that is the desire (XA-compatibility) then PostgreSQL might be
talking to an Oracle database or a BEA Tuxedo TPM acting as the
coordinator. So PostgreSQL won't have an opportunity to modify the
protocol in any meaningful way if it wishes to interoperate with
XA-based transaction managers.

If it is being used only amongst other PostgreSQL backends for
replication, then why not use one of the optimistic replication protocols:

http://www.inf.ethz.ch/personal/alonso/PAPERS/commit-fast.pdf

Mike Mascari
[EMAIL PROTECTED]



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

   http://www.postgresql.org/docs/faqs/FAQ.html

Re: [HACKERS] initdb failure (was Re: [GENERAL] sequence's plpgsql)

[Tom Lane]

Peter Eisentraut <[EMAIL PROTECTED]> writes:
>> Minimalism isn't really a virtue in error reports anyway.
>> I'm thinking maybe:
>> CONTEXT:  COPY tablename, line 41: "data ..."
>> would serve the purpose nicely.

> The only thing that would really help in the general case is the number of
> the character where the error occurs.  If you print the actual data you
> lose if the data is repeated within the, er, data and/or if the section of
> the data that you print contains crazy characters that mess up the
> display.

A messed-up display would be useful information in itself; and it's not
like we have no precedent for repeating the erroneous data in error
messages.  Consider pg_atoi:

regression=# create table foo (f1 int);
CREATE TABLE
regression=# copy foo from stdin;
Enter data to be copied followed by a newline.
End with a backslash and a period on a line by itself.
>> z44
>> \.
ERROR:  invalid input syntax for integer: "z44"
CONTEXT:  COPY FROM, line 1
regression=#

I was chewing this over with Bruce on the phone just now, and we refined
the idea a little.  Some errors (primarily those detected inside the
datatype input procedures) can be clearly traced to a specific column,
whereas others (such as too many fields on an input line) really can't
be nailed down more tightly than a line.  So we were thinking about two
different flavors of context info:

CONTEXT:  COPY tablename, line n, field colname: "column data"

versus

CONTEXT:  COPY tablename, line n: "line data"

where in each case the data display would be truncated at 100 characters
or so (and would have to be omitted in the COPY BINARY case anyway).

If you're really concerned about funny characters messing up the report,
we could imagine replacing them by backslash sequences or some such, but
I suspect that would create more confusion than it would solve.

regards, tom lane

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

   http://archives.postgresql.org

Re: [HACKERS] initdb failure (was Re: [GENERAL] sequence's plpgsql)

[Bruce Momjian]

Peter Eisentraut wrote:
> scott.marlowe writes:
> 
> > table name too, like Bruce said.  The bothersome bit is that in pg_dump,
> > it says the line, relative to just this part of the copy command, so you
> > don't even know which table is giving the error.
> 
> I don't see the problem.  Can't you identify the failing command by the
> line number that psql gives you?

If you are loading from pg_dump, you have lots of copy commands, so how
do you know which COPY command caused the failure.  You just have the
line number of _a_ copy.

-- 
  Bruce Momjian|  http://candle.pha.pa.us
  [EMAIL PROTECTED]   |  (610) 359-1001
  +  If your life is a hard drive, |  13 Roberts Road
  +  Christ can be your backup.|  Newtown Square, Pennsylvania 19073

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

   http://www.postgresql.org/docs/faqs/FAQ.html

Re: [HACKERS] initdb failure (was Re: [GENERAL] sequence's plpgsql)

[Bruce Momjian]

Peter Eisentraut wrote:
> Bruce Momjian writes:
> 
> > The argument that you want a warning because you might have mixed
> > newlines in the file seems less likely than this case where they are
> > using a literal carriage return as a data value at the end of the line.
> 
> I don't agree with that assessment.  Who actually has CRs in their data?

Uh, people storing Win32 text documents.

-- 
  Bruce Momjian|  http://candle.pha.pa.us
  [EMAIL PROTECTED]   |  (610) 359-1001
  +  If your life is a hard drive, |  13 Roberts Road
  +  Christ can be your backup.|  Newtown Square, Pennsylvania 19073

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

Re: [HACKERS] initdb failure (was Re: [GENERAL] sequence's plpgsql)

[Peter Eisentraut]

Tom Lane writes:

> This doesn't seem like a good argument not to add more information to
> the CONTEXT line for COPY errors.  Sure, in theory the existing info
> should be sufficient, but what if the information is not coming in
> through psql?  (For instance, maybe the COPY data is being generated
> on-the-fly by some other program.)

Then you look into the code of that other program.  Or you look into the
server log, where you should log the statements you generate if you are
testing your code.

> Or what if the dump file is so large you can't easily edit it to
> determine which line number is in question?

The line number is already contained in the available information about
the error.  If the file is too large to load into an editor, you could use

perl -npi -e '$. == 123456 and s/\r/\\r/;'

or something along those lines.

> There are plenty of scenarios where it's not all that convenient to
> triangulate on a problem from outside information.

Maybe, but the ones I've seen mentioned so far are not among them.  I'm
not opposed to making errors more easy to identify, but considering that
someone else in this thread didn't even know about psql's option to print
line numbers of errors, I think some people haven't done their homework
yet.

> Minimalism isn't really a virtue in error reports anyway.
>
> I'm thinking maybe:
>
>   CONTEXT:  COPY tablename, line 41: "data ..."
>
> would serve the purpose nicely.

The only thing that would really help in the general case is the number of
the character where the error occurs.  If you print the actual data you
lose if the data is repeated within the, er, data and/or if the section of
the data that you print contains crazy characters that mess up the
display.

-- 
Peter Eisentraut   [EMAIL PROTECTED]


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

Re: [HACKERS] initdb failure (was Re: [GENERAL] sequence's plpgsql)

[Tom Lane]

I said:
> The question isn't so much "who has CRs in their data" as "who is trying
> to import data files in which CRs aren't correctly represented as \r" ?
> Not anyone upgrading from a recent PG release ... though 7.1 or before
> would have an issue.

Actually, checking the CVS logs shows that 7.2.1 was the first release
that would emit a data CR or LF as \r or \n rather than literally.  If
Scott is dumping from an original 7.2 installation rather than a dot-
release, that would explain his report.

I talked to Bruce about this on the phone, and we now both feel that
that change was too recent to assume that people won't be trying to load
dumps containing bare CRs into 7.4.  Accordingly, it seems that we do
need to throw an error or at least a warning for mixed newlines.
Eventually we can get rid of that behavior and just treat all newline
sequences alike, but I think we can't do it until 7.2.0 is in the
"ancient history" category.

regards, tom lane

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

Re: [HACKERS] initdb failure (was Re: [GENERAL] sequence's plpgsql)

[Peter Eisentraut]

scott.marlowe writes:

> psql:webport.sql:803: ERROR:  function "odbc_user" already exists with
> same argument types
> REVOKE
> REVOKE
> GRANT
> You are now connected as new user "ayousuff".
> psql:webport.sql:869: ERROR:  literal newline found in data
> HINT:  Use "\n" to represent newline.
> CONTEXT:  COPY FROM, line 59
> You are now connected as new user "smarlowe".
> You are now connected as new user "ayousuff".
> CREATE INDEX
> CREATE INDEX
> CREATE INDEX
>
>
> the same.  It doesn't tell me which table in my dump caused the problem,
> and it certainly isn't line 59 of the dump file, but of the table
> producing the error.

The table is the one the COPY command on line 869 in file webport.sql
mentions and the problem data is on line 928 of that file.

-- 
Peter Eisentraut   [EMAIL PROTECTED]


---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

Re: [HACKERS] 2-phase commit

[Rod Taylor]

> The first problem is the restart/rejoin problem.  When a 2PC member
> goes away, it is supposed to come back with all its former locks and
> everything in place, so that it can know what to do.  This is also
> extremely tricky, but I think the answer is sort of easy.  A member
> which re-joins without crashing (that is, it has open transactions,

I think you may be confusing 2PC with replication.

PostgreSQLs 2PC implementation should follow enough of the XA rules to
play nice in a mixed environment where something else is managing the
transactions (application servers are becoming more common all the
time).

As far as inter-PostgreSQL replication / queries are concerned we can
choose whatever semantics we like -- just realize that they are 2
different problems.


signature.asc
Description: This is a digitally signed message part

Re: [HACKERS] initdb failure (was Re: [GENERAL] sequence's plpgsql)

[Tom Lane]

Peter Eisentraut <[EMAIL PROTECTED]> writes:
> Bruce Momjian writes:
>> The argument that you want a warning because you might have mixed
>> newlines in the file seems less likely than this case where they are
>> using a literal carriage return as a data value at the end of the line.

> I don't agree with that assessment.  Who actually has CRs in their data?

The question isn't so much "who has CRs in their data" as "who is trying
to import data files in which CRs aren't correctly represented as \r" ?
Not anyone upgrading from a recent PG release ... though 7.1 or before
would have an issue.

If we eliminate the mixed-newline check then we will silently discard
CRs that appear at the tail end of the last column of a data line.  This
is somewhat worrisome because the failure will not be obvious.  But in
most cases (CR in the middle of a data field, or in a field that isn't
last), we will detect and report an error due to field count mismatch.
I think that that might be good enough.

If we were designing COPY from scratch today, we'd certainly not think
that a mixed-newline check was appropriate; it's only because of our
historical handling of literal CRs that the question comes up at all.

regards, tom lane

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

Re: [HACKERS] initdb failure (was Re: [GENERAL] sequence's plpgsql)

[scott.marlowe]

On Fri, 26 Sep 2003, Peter Eisentraut wrote:

> scott.marlowe writes:
> 
> > table name too, like Bruce said.  The bothersome bit is that in pg_dump,
> > it says the line, relative to just this part of the copy command, so you
> > don't even know which table is giving the error.
> 
> I don't see the problem.  Can't you identify the failing command by the
> line number that psql gives you?

OK, here's the output from pg_dump while it's running

ERROR:  function "odbc_user" already exists with same argument types
REVOKE
REVOKE
GRANT
You are now connected as new user "ayousuff".
ERROR:  literal newline found in data
HINT:  Use "\n" to represent newline.
CONTEXT:  COPY FROM, line 59
You are now connected as new user "smarlowe".
You are now connected as new user "ayousuff".
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX
You are now connected as new user "smarlowe".
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX

So how am I supposed to figure out which table and which row broke?  Keep 
in mind, I'm doing this:

pg_dump -h otherserver dbname|psql -h desthost dbname

but I get basically the same thing if I dump it to a .sql file and do:

psql dbname 

Re: [HACKERS] initdb failure (was Re: [GENERAL] sequence's plpgsql)

[scott.marlowe]

On Fri, 26 Sep 2003, Tom Lane wrote:

> Peter Eisentraut <[EMAIL PROTECTED]> writes:
> > scott.marlowe writes:
> >> but I get basically the same thing if I dump it to a .sql file and do:
> >> psql dbname  
> > Use psql -f dbname.sql instead.
> 
> This doesn't seem like a good argument not to add more information to
> the CONTEXT line for COPY errors.  Sure, in theory the existing info
> should be sufficient, but what if the information is not coming in
> through psql?  (For instance, maybe the COPY data is being generated
> on-the-fly by some other program.)  Or what if the dump file is so large
> you can't easily edit it to determine which line number is in question?
> There are plenty of scenarios where it's not all that convenient to
> triangulate on a problem from outside information.  Minimalism isn't
> really a virtue in error reports anyway.
> 
> I'm thinking maybe:
> 
>   CONTEXT:  COPY tablename, line 41: "data ..."
> 
> would serve the purpose nicely.

Yeah, just having the table name and line number would be plenty for me.  
It's the lack of a table name that makes it so frustrating.  I had to 
basically dump / restore the tables one at a time to figure out which one 
was causing the error.  On a database with hundreds of tables, that could 
be painful.


---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

Re: [HACKERS] initdb failure (was Re: [GENERAL] sequence's plpgsql)

[scott.marlowe]

On Fri, 26 Sep 2003, Peter Eisentraut wrote:

> scott.marlowe writes:
> 
> > but I get basically the same thing if I dump it to a .sql file and do:
> >
> > psql dbname  
> Use psql -f dbname.sql instead.

and the output is:

psql:webport.sql:803: ERROR:  function "odbc_user" already exists with 
same argument types
REVOKE
REVOKE
GRANT
You are now connected as new user "ayousuff".
psql:webport.sql:869: ERROR:  literal newline found in data
HINT:  Use "\n" to represent newline.
CONTEXT:  COPY FROM, line 59
You are now connected as new user "smarlowe".
You are now connected as new user "ayousuff".
CREATE INDEX
CREATE INDEX
CREATE INDEX


the same.  It doesn't tell me which table in my dump caused the problem, 
and it certainly isn't line 59 of the dump file, but of the table 
producing the error.


---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

Re: [HACKERS] initdb failure (was Re: [GENERAL] sequence's plpgsql)

[Tom Lane]

Peter Eisentraut <[EMAIL PROTECTED]> writes:
> scott.marlowe writes:
>> but I get basically the same thing if I dump it to a .sql file and do:
>> psql dbname  Use psql -f dbname.sql instead.

This doesn't seem like a good argument not to add more information to
the CONTEXT line for COPY errors.  Sure, in theory the existing info
should be sufficient, but what if the information is not coming in
through psql?  (For instance, maybe the COPY data is being generated
on-the-fly by some other program.)  Or what if the dump file is so large
you can't easily edit it to determine which line number is in question?
There are plenty of scenarios where it's not all that convenient to
triangulate on a problem from outside information.  Minimalism isn't
really a virtue in error reports anyway.

I'm thinking maybe:

CONTEXT:  COPY tablename, line 41: "data ..."

would serve the purpose nicely.

regards, tom lane

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

Re: [HACKERS] 2-phase commit

[Rod Taylor]

On Fri, 2003-09-26 at 13:58, Bruce Momjian wrote:
> Patrick Welche wrote:
> > On Fri, Sep 26, 2003 at 02:49:30PM -0300, Marc G. Fournier wrote:
> > ... 
> > > if we are talking two computers sitting next to each other on a switch,
> > > you'd expect those to be low ... but if you were talking about two
> > > seperate geographical locations (and yes, I realize you are adding lag to
> > > the mix with waiting for responses), you'd expect those #s to rise ...
> > 
> > Which I thought was the whole point of using a group communication protocol
> > such as spread in postgresql-r. It seemed solved there...
> 
> Right, but I think we want to try to do two-phase commit without spread.
> Spread seems overkill for this usage.

Out of curiosity, how does one use spread to accomplish 2PC? Isn't the
logic the Application Server would need to follow rather different with
a group communication based control than with XA / 2PC style
communication? How does one map to the other?


signature.asc
Description: This is a digitally signed message part

Re: [HACKERS] initdb failure (was Re: [GENERAL] sequence's plpgsql)

[Larry Rosenman]

--On Friday, September 26, 2003 14:36:08 -0600 "scott.marlowe" 
<[EMAIL PROTECTED]> wrote:

On Fri, 26 Sep 2003, Peter Eisentraut wrote:

Bruce Momjian writes:

> The argument that you want a warning because you might have mixed
> newlines in the file seems less likely than this case where they are
> using a literal carriage return as a data value at the end of the line.
I don't agree with that assessment.  Who actually has CRs in their data?
I do.  And so do lots of other people.
I store E-Mail type comments in some of my DB's and they may contain cr's.

Don't ASSUME that we don't do stuff like this with a database.



--
Larry Rosenman http://www.lerctr.org/~ler
Phone: +1 972-414-9812 E-Mail: [EMAIL PROTECTED]
US Mail: 1905 Steamboat Springs Drive, Garland, TX 75044-6749


pgp0.pgp
Description: PGP signature

Re: [HACKERS] initdb failure (was Re: [GENERAL] sequence's plpgsql)

[Peter Eisentraut]

scott.marlowe writes:

> but I get basically the same thing if I dump it to a .sql file and do:
>
> psql dbname 

Re: [HACKERS] initdb failure (was Re: [GENERAL] sequence's plpgsql)

[scott.marlowe]

On Fri, 26 Sep 2003, Peter Eisentraut wrote:

> Bruce Momjian writes:
> 
> > The argument that you want a warning because you might have mixed
> > newlines in the file seems less likely than this case where they are
> > using a literal carriage return as a data value at the end of the line.
> 
> I don't agree with that assessment.  Who actually has CRs in their data?

I do.  And so do lots of other people.


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

   http://archives.postgresql.org

Re: [HACKERS] initdb failure (was Re: [GENERAL] sequence's plpgsql)

[Peter Eisentraut]

Bruce Momjian writes:

> The argument that you want a warning because you might have mixed
> newlines in the file seems less likely than this case where they are
> using a literal carriage return as a data value at the end of the line.

I don't agree with that assessment.  Who actually has CRs in their data?

-- 
Peter Eisentraut   [EMAIL PROTECTED]


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

Re: [HACKERS] initdb failure (was Re: [GENERAL] sequence's plpgsql)

[Peter Eisentraut]

scott.marlowe writes:

> table name too, like Bruce said.  The bothersome bit is that in pg_dump,
> it says the line, relative to just this part of the copy command, so you
> don't even know which table is giving the error.

I don't see the problem.  Can't you identify the failing command by the
line number that psql gives you?

-- 
Peter Eisentraut   [EMAIL PROTECTED]


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

Re: [HACKERS] initdb failure (was Re: [GENERAL] sequence's plpgsql)

[scott.marlowe]

On Fri, 26 Sep 2003, Bruce Momjian wrote:

> scott.marlowe wrote:
> > > OK, 'vi' shows it as:
> > >   
> > >   COPY people2 (id, persons) FROM stdin;
> > >   59  Chance Terry--S
> > >   60  ^M
> > >   \.
> > > 
> > > which is _exactly the case the error was supposed to catch.  Now, the
> > > big question is where did this dump come from?  Pg version?  OS platform?
> > 
> > The originating system is a RedHat 7.2 box with postgresql 7.2.x running 
> > on it.
> > 
> > The destination system is a RedHat 7.2 box with postgresql 7.4 beta3 
> > running on it.
> > 
> > The data likely came out of a (gasp, horrors) windows box.
> 
> OK, try this on your 7.2:
> 
>   test=> create table test(x text);
>   CREATE TABLE
>   test=> insert into test values ('\r');
>   INSERT 17158 1
>   test=> copy test to '/tmp/out';
>   COPY
> 
> Then 'vi' /tmp/out. It should show \r, not ^M.  Please report back.

Figured out iv (-b switch, man pages rock) and it's a ^M



---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

Re: [HACKERS] initdb failure (was Re: [GENERAL] sequence's plpgsql)

[scott.marlowe]

On Fri, 26 Sep 2003, Bruce Momjian wrote:

> scott.marlowe wrote:
> > > OK, 'vi' shows it as:
> > >   
> > >   COPY people2 (id, persons) FROM stdin;
> > >   59  Chance Terry--S
> > >   60  ^M
> > >   \.
> > > 
> > > which is _exactly the case the error was supposed to catch.  Now, the
> > > big question is where did this dump come from?  Pg version?  OS platform?
> > 
> > The originating system is a RedHat 7.2 box with postgresql 7.2.x running 
> > on it.
> > 
> > The destination system is a RedHat 7.2 box with postgresql 7.4 beta3 
> > running on it.
> > 
> > The data likely came out of a (gasp, horrors) windows box.
> 
> OK, try this on your 7.2:
> 
>   test=> create table test(x text);
>   CREATE TABLE
>   test=> insert into test values ('\r');
>   INSERT 17158 1
>   test=> copy test to '/tmp/out';
>   COPY
> 
> Then 'vi' /tmp/out. It should show \r, not ^M.  Please report back.

I'm not much of a vi guy, so the out file thing didn't tell me much, but 
if I try and dump it from the 7.4 beta3 box into a new table, I get:

ERROR:  end-of-copy marker does not match previous newline style
CONTEXT:  COPY FROM, line 2



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

Re: [HACKERS] 2-phase commit

[Marc G. Fournier]

On Fri, 26 Sep 2003, Christopher Browne wrote:

> [EMAIL PROTECTED] (Bruce Momjian) writes:
> > Patrick Welche wrote:
> >> On Fri, Sep 26, 2003 at 02:49:30PM -0300, Marc G. Fournier wrote:
> >> ...
> >> > if we are talking two computers sitting next to each other on a switch,
> >> > you'd expect those to be low ... but if you were talking about two
> >> > seperate geographical locations (and yes, I realize you are adding lag to
> >> > the mix with waiting for responses), you'd expect those #s to rise ...
> >>
> >> Which I thought was the whole point of using a group communication
> >> protocol such as spread in postgresql-r. It seemed solved there...
> >
> > Right, but I think we want to try to do two-phase commit without
> > spread.  Spread seems overkill for this usage.
>
> Is there some big demerit to _having_ that "overkill"?  If there is no
> major price to pay, then I don't see why it isn't reasonable to simply
> say "Sure, we'll use that!"

Reliance on a third party library to be installed to provide the
functionality ... if it were meant as an "add on" instead of "standard
feature", then sure ...

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

Re: [HACKERS] initdb failure (was Re: [GENERAL] sequence's plpgsql)

[Michael Meskes]

On Fri, Sep 26, 2003 at 03:05:58PM -0400, Tom Lane wrote:
> Not sure if we should make the behavior Windows-specific though.  And
> didn't Michael report seeing the same initdb failure on Debian?  That
> confuses me a bit --- why would there be a newline discrepancy on Debian?

I take it there are no special characters in that COPY, right? The only
difference between the two machines we found was that my was running
under UTF-8. 

Michael
-- 
Michael Meskes
Email: Michael at Fam-Meskes dot De
ICQ: 179140304, AIM/Yahoo: michaelmeskes, Jabber: [EMAIL PROTECTED]
Go SF 49ers! Go Rhein Fire! Use Debian GNU/Linux! Use PostgreSQL!

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

Re: [HACKERS] initdb failure (was Re: [GENERAL] sequence's plpgsql)

[Bruce Momjian]

scott.marlowe wrote:
> > OK, 'vi' shows it as:
> > 
> > COPY people2 (id, persons) FROM stdin;
> > 59  Chance Terry--S
> > 60  ^M
> > \.
> > 
> > which is _exactly the case the error was supposed to catch.  Now, the
> > big question is where did this dump come from?  Pg version?  OS platform?
> 
> The originating system is a RedHat 7.2 box with postgresql 7.2.x running 
> on it.
> 
> The destination system is a RedHat 7.2 box with postgresql 7.4 beta3 
> running on it.
> 
> The data likely came out of a (gasp, horrors) windows box.

OK, try this on your 7.2:

test=> create table test(x text);
CREATE TABLE
test=> insert into test values ('\r');
INSERT 17158 1
test=> copy test to '/tmp/out';
COPY

Then 'vi' /tmp/out. It should show \r, not ^M.  Please report back.

-- 
  Bruce Momjian|  http://candle.pha.pa.us
  [EMAIL PROTECTED]   |  (610) 359-1001
  +  If your life is a hard drive, |  13 Roberts Road
  +  Christ can be your backup.|  Newtown Square, Pennsylvania 19073

---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

Re: [HACKERS] initdb failure (was Re: [GENERAL] sequence's plpgsql)

[scott.marlowe]

On Fri, 26 Sep 2003, Bruce Momjian wrote:

> scott.marlowe wrote:
> > The attached file produces this problem.  Note it's a blank trailing field 
> > that looks to be causing it.  The error for this .sql file is:
> > 
> > ERROR:  literal carriage return found in data
> > HINT:  Use "\r" to represent carriage return.
> > CONTEXT:  COPY FROM, line 2
> > 
> > Note that loading this into pico and saving it back out fixes the problem.
> > 
> > If I remove the preceding row that doesn't end in a blank field, I get a 
> > different error, this one:
> > 
> > ERROR:  end-of-copy marker does not match previous newline style
> > CONTEXT:  COPY FROM, line 2
> 
> OK, 'vi' shows it as:
>   
>   COPY people2 (id, persons) FROM stdin;
>   59  Chance Terry--S
>   60  ^M
>   \.
> 
> which is _exactly the case the error was supposed to catch.  Now, the
> big question is where did this dump come from?  Pg version?  OS platform?

The originating system is a RedHat 7.2 box with postgresql 7.2.x running 
on it.

The destination system is a RedHat 7.2 box with postgresql 7.4 beta3 
running on it.

The data likely came out of a (gasp, horrors) windows box.


---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

Re: [HACKERS] 2-phase commit

[Andrew Sullivan]

On Fri, Sep 26, 2003 at 02:05:36PM -0400, Tom Lane wrote:
> a valid slave anymore.  You can make this work, but the resource costs
> are steep.  For instance, in Postgres, you don't get to truncate the WAL

But people who want 2PC are more than willing to pay all that cost. 

A
-- 

Andrew Sullivan 204-4141 Yonge Street
Afilias CanadaToronto, Ontario Canada
<[EMAIL PROTECTED]>  M2P 2A8
 +1 416 646 3304 x110


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

Re: [HACKERS] initdb failure (was Re: [GENERAL] sequence's plpgsql)

[Bruce Momjian]

Bruce Momjian wrote:
> scott.marlowe wrote:
> > The attached file produces this problem.  Note it's a blank trailing field 
> > that looks to be causing it.  The error for this .sql file is:
> > 
> > ERROR:  literal carriage return found in data
> > HINT:  Use "\r" to represent carriage return.
> > CONTEXT:  COPY FROM, line 2
> > 
> > Note that loading this into pico and saving it back out fixes the problem.
> > 
> > If I remove the preceding row that doesn't end in a blank field, I get a 
> > different error, this one:
> > 
> > ERROR:  end-of-copy marker does not match previous newline style
> > CONTEXT:  COPY FROM, line 2
> 
> OK, 'vi' shows it as:
>   
>   COPY people2 (id, persons) FROM stdin;
>   59  Chance Terry--S
>   60  ^M
>   \.

The significant issue here is that we don't want to throw a warning in
this case;  it should be an error --- this is clearly a data value we
don't want to discard as part of the end-of-line.

The argument that you want a warning because you might have mixed
newlines in the file seems less likely than this case where they are
using a literal carriage return as a data value at the end of the line.

Seems we should just fix initdb as my patch does and see if we get any
more mixed-newline cases or literal carriage-return data cases.  My
guess is that we will get more of the latter.

-- 
  Bruce Momjian|  http://candle.pha.pa.us
  [EMAIL PROTECTED]   |  (610) 359-1001
  +  If your life is a hard drive, |  13 Roberts Road
  +  Christ can be your backup.|  Newtown Square, Pennsylvania 19073

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

   http://archives.postgresql.org

Re: [HACKERS] 2-phase commit

[Andrew Sullivan]

On Fri, Sep 26, 2003 at 01:34:28PM -0400, Tom Lane wrote:
> 
> Example:
> 
>   Master  Slave
>   --  -
>   commit ready-->
>   <--OK
>   commit done->XX

> maybe he didn't.  Both sides are forced to keep information about the 
> open transaction indefinitely.  Timing out on either side could yield
> the wrong result.

If i understand the complaints, I think there are two big issues.

The first problem is the restart/rejoin problem.  When a 2PC member
goes away, it is supposed to come back with all its former locks and
everything in place, so that it can know what to do.  This is also
extremely tricky, but I think the answer is sort of easy.  A member
which re-joins without crashing (that is, it has open transactions,
&c.), it just has to complete its transactions with the other
member(s).  If other members have processed new transactions since
the member left, the member is kicked out.  It's not allowed to join
without being re-initialised.  A member which crashes is just a
special case of this.  This is not elegant, not nice, &c.  But I
don't think anyone can really guarantee that a crahsed member will
start up correctly (it crashed, after all; maybe there's a bug).  So
this is the safest approach, and I don't think it's a big deal.  It's
not cheap, of course, and there may be problems arising from the
conditions I describe below.  But I think they can be handled (see
the section on "compromises", below) intelligently.

The second, stickier problem is just as Tom describes.  When the
master is "Commit done" and that message doesn't make it to the other
host(s), you might have to wait forever.  Of course, that's not
acceptable.

But I can think of some options of how to decide to handle this. 
Note that these may not guarantee no loss of data.  That's not a
compromise one is usually willing to make; but just because I don't
want to accept that compromise doesn't mean it is unacceptable to
everyone.

Some possible compromises
=

1,  One machine always wins.  One could decide to pick one
machine that, in case of some sort of failure, always wins.  You need
some sort of heartbeat system which checks for the other member(s) of
the cluster.  In the event of failure, whatever is on the "winner"
machine is deemed to be correct, and everyone else has to lose.  If
the point of your 2PC is to provide synchronous access to high loads
of read-only clients, this would probably be a good solution, since
only one machine would ever see data changes.

2.  Quorum rule.  One could decide on a quorum of machines, and
the group which has quorum wins.  (Naturally, this has to be an
absolute majority.)  The quorum can continue to process queries, and
the folks who left the room have to re-sync to join.

3.  Fail to read-only status and let the DBA sort it out.  

4.  Mark the contentious rows as "bad" and let the DBA sort it
out.  This option is not dissimilar to what Access/SQL server
disconnected multi-master replication does.  It's not elegant, but it
might be a good answer for the cases where 2PC gets used.

Note that none of these can guarantee that some apparently committed
data will not later be lost.  To real database hounds, that will
sound like apostasy, but I suspect it is the sort of trade-off that
real products make all the time.  You have to have a way of
collecting the "yeah, we told you it was committed, but we lied" data
and being able to track it; and that has to be enough.  The real
security-of-data work is going to have to be done by ultra-reliable
hardware, good maintenance practices, &c.  Then when losses are down
in the .001% range from this sort of mistake, no one will care.

This is not, by the way, the fully-formed set of suggestions I said
I'd deliver when I started the thread; but since it came up again
today, I thought I'd respond with what I had so far.

A

-- 

Andrew Sullivan 204-4141 Yonge Street
Liberty RMS   Toronto, Ontario Canada
<[EMAIL PROTECTED]>  M2P 2A8
 +1 416 646 3304 x110


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

Re: [HACKERS] initdb failure (was Re: [GENERAL] sequence's plpgsql)

[Bruce Momjian]

scott.marlowe wrote:
> The attached file produces this problem.  Note it's a blank trailing field 
> that looks to be causing it.  The error for this .sql file is:
> 
> ERROR:  literal carriage return found in data
> HINT:  Use "\r" to represent carriage return.
> CONTEXT:  COPY FROM, line 2
> 
> Note that loading this into pico and saving it back out fixes the problem.
> 
> If I remove the preceding row that doesn't end in a blank field, I get a 
> different error, this one:
> 
> ERROR:  end-of-copy marker does not match previous newline style
> CONTEXT:  COPY FROM, line 2

OK, 'vi' shows it as:

COPY people2 (id, persons) FROM stdin;
59  Chance Terry--S
60  ^M
\.

which is _exactly the case the error was supposed to catch.  Now, the
big question is where did this dump come from?  Pg version?  OS platform?

-- 
  Bruce Momjian|  http://candle.pha.pa.us
  [EMAIL PROTECTED]   |  (610) 359-1001
  +  If your life is a hard drive, |  13 Roberts Road
  +  Christ can be your backup.|  Newtown Square, Pennsylvania 19073

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

Re: [HACKERS] initdb failure (was Re: [GENERAL] sequence's plpgsql)

[scott.marlowe]

On Fri, 26 Sep 2003, Tom Lane wrote:

> "scott.marlowe" <[EMAIL PROTECTED]> writes:
> > I'm running into issues where 7.4's pg_dump/pg_dumpall from a 7.2 database 
> > to a 7.4beta3 database is producing some errors like this:
> 
> > ERROR:  literal newline found in data
> > HINT:  Use "\n" to represent newline.
> > CONTEXT:  COPY FROM, line 59
> 
> > ERROR:  literal carriage return found in data
> > HINT:  Use "\r" to represent carriage return.
> > CONTEXT:  COPY FROM, line 41
> 
> Really?  7.2 should dump data \r or \n as the backslash versions ...
> and does in my tests.  Can you make a reproducible test case?

The attached file produces this problem.  Note it's a blank trailing field 
that looks to be causing it.  The error for this .sql file is:

ERROR:  literal carriage return found in data
HINT:  Use "\r" to represent carriage return.
CONTEXT:  COPY FROM, line 2

Note that loading this into pico and saving it back out fixes the problem.

If I remove the preceding row that doesn't end in a blank field, I get a 
different error, this one:

ERROR:  end-of-copy marker does not match previous newline style
CONTEXT:  COPY FROM, line 2

> > It would be nice to have such occurances echo the table / row they are
> > getting the error on, or maybe just the first 20 or so characters, so
> > they'd be easier to identify.
> 
> That's not a bad idea.  I think it would be fairly easy now for the
> CONTEXT line of the error message to include the input data line:
> 
>   CONTEXT:  COPY FROM, line 41: "data here "
> 
> at least up through the field where the error gets thrown, and with some
> limit on the length of the data that will get echoed.  If people like
> that idea I'll see about making it happen.

table name too, like Bruce said.  The bothersome bit is that in pg_dump, 
it says the line, relative to just this part of the copy command, so you 
don't even know which table is giving the error.
--
-- PostgreSQL database dump
--

\connect - marl8412

--
-- TOC entry 2 (OID 283043147)
-- Name: people2; Type: TABLE; Schema: ; Owner: marl8412
--

CREATE TABLE people2 (
id integer,
persons text
);


--
-- Data for TOC entry 3 (OID 283043147)
-- Name: people2; Type: TABLE DATA; Schema: ; Owner: marl8412
--

COPY people2 (id, persons) FROM stdin;
59  Chance Terry--S
60  

\.



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

Re: [HACKERS] invalid tid errors in latest 7.3.4 stable.

[Stephan Szabo]

On Fri, 26 Sep 2003, Tom Lane wrote:

> Stephan Szabo <[EMAIL PROTECTED]> writes:
> > I think theoretically in serializable the cases where the difference
> > between the snapshot from this statement and the standard snapshot for the
> > transaction are noticable we probably have a serialization failure
>
> Hmm, that is a good point.  It would be cleaner to throw a "can't
> serialize" failure than have the RI triggers run under a different
> snapshot.  I am not sure if we can implement that behavior easily,
> though.  Can you think of a way to detect whether there's an RI conflict
> against a later-started transaction?

Not a complete one yet. :(
I think the case of a row that matches the constraint's search condition
on either check or action but which is committed and invisible to our
snapshot (which for read committed is taken at some point after the
original row modification that this was triggered by) is an error may
cover the basic cases, but I don't feel confident that I'm not
missing some trigger/rule case.



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

Re: [HACKERS] 2-phase commit

[Christopher Browne]

[EMAIL PROTECTED] (Bruce Momjian) writes:
> Patrick Welche wrote:
>> On Fri, Sep 26, 2003 at 02:49:30PM -0300, Marc G. Fournier wrote:
>> ... 
>> > if we are talking two computers sitting next to each other on a switch,
>> > you'd expect those to be low ... but if you were talking about two
>> > seperate geographical locations (and yes, I realize you are adding lag to
>> > the mix with waiting for responses), you'd expect those #s to rise ...
>> 
>> Which I thought was the whole point of using a group communication
>> protocol such as spread in postgresql-r. It seemed solved there...
>
> Right, but I think we want to try to do two-phase commit without
> spread.  Spread seems overkill for this usage.

Is there some big demerit to _having_ that "overkill"?  If there is no
major price to pay, then I don't see why it isn't reasonable to simply
say "Sure, we'll use that!"

After all, PostgreSQL is set up to do _everything_ inside
transactions, even though there are some actions you might take that
don't forcibly need to be transactional.  That's overkill, and nobody
(well, barring fans of Certain Other Databases) complains that it's
overkill.
-- 
let name="cbbrowne" and tld="libertyrms.info" in String.concat "@" [name;tld];;

Christopher Browne
(416) 646 3304 x124 (land)

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

Re: [HACKERS] initdb failure

[Andrew Dunstan]

Tom Lane wrote:

I don't mind if we keep it on pure-POSIX platforms.  But one of the nicer
developments on Windows in recent(?) times is that you can actually use
any kind of line separator and most programs still work correctly (with
the notable exception of Notepad).
   

Not sure if we should make the behavior Windows-specific though.  And
didn't Michael report seeing the same initdb failure on Debian?  That
confuses me a bit --- why would there be a newline discrepancy on Debian?
 

Is this affected by the fact that the platform where a dump is made 
might have a different line-end discipline from the one where the dump 
is restored?

cheers

andrew



---(end of broadcast)---
TIP 6: Have you searched our list archives?
  http://archives.postgresql.org

Re: [HACKERS] initdb failure (was Re: [GENERAL] sequence's plpgsql)

[Tom Lane]

Peter Eisentraut <[EMAIL PROTECTED]> writes:
> Tom Lane writes:
>> Yeah, I was wondering whether you wouldn't propose dropping the newline
>> consistency check.  I'm not very comfortable with that, but maybe we
>> should.  Bruce?

> I don't mind if we keep it on pure-POSIX platforms.  But one of the nicer
> developments on Windows in recent(?) times is that you can actually use
> any kind of line separator and most programs still work correctly (with
> the notable exception of Notepad).

Not sure if we should make the behavior Windows-specific though.  And
didn't Michael report seeing the same initdb failure on Debian?  That
confuses me a bit --- why would there be a newline discrepancy on Debian?

regards, tom lane

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

Re: [HACKERS] initdb failure (was Re: [GENERAL] sequence's plpgsql)

[Bruce Momjian]

Tom Lane wrote:
> > ERROR:  literal carriage return found in data
> > HINT:  Use "\r" to represent carriage return.
> > CONTEXT:  COPY FROM, line 41
> 
> Really?  7.2 should dump data \r or \n as the backslash versions ...
> and does in my tests.  Can you make a reproducible test case?
> 
> 
> 
> > It would be nice to have such occurances echo the table / row they are
> > getting the error on, or maybe just the first 20 or so characters, so
> > they'd be easier to identify.
> 
> That's not a bad idea.  I think it would be fairly easy now for the
> CONTEXT line of the error message to include the input data line:
> 
>   CONTEXT:  COPY FROM, line 41: "data here "
> 
> at least up through the field where the error gets thrown, and with some
> limit on the length of the data that will get echoed.  If people like
> that idea I'll see about making it happen.

Also, he wanted table name too.

-- 
  Bruce Momjian|  http://candle.pha.pa.us
  [EMAIL PROTECTED]   |  (610) 359-1001
  +  If your life is a hard drive, |  13 Roberts Road
  +  Christ can be your backup.|  Newtown Square, Pennsylvania 19073

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

Re: [HACKERS] initdb failure (was Re: [GENERAL] sequence's plpgsql)

[Tom Lane]

"scott.marlowe" <[EMAIL PROTECTED]> writes:
> I'm running into issues where 7.4's pg_dump/pg_dumpall from a 7.2 database 
> to a 7.4beta3 database is producing some errors like this:

> ERROR:  literal newline found in data
> HINT:  Use "\n" to represent newline.
> CONTEXT:  COPY FROM, line 59

> ERROR:  literal carriage return found in data
> HINT:  Use "\r" to represent carriage return.
> CONTEXT:  COPY FROM, line 41

Really?  7.2 should dump data \r or \n as the backslash versions ...
and does in my tests.  Can you make a reproducible test case?



> It would be nice to have such occurances echo the table / row they are
> getting the error on, or maybe just the first 20 or so characters, so
> they'd be easier to identify.

That's not a bad idea.  I think it would be fairly easy now for the
CONTEXT line of the error message to include the input data line:

CONTEXT:  COPY FROM, line 41: "data here "

at least up through the field where the error gets thrown, and with some
limit on the length of the data that will get echoed.  If people like
that idea I'll see about making it happen.

regards, tom lane

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

   http://archives.postgresql.org

Re: [HACKERS] initdb failure (was Re: [GENERAL] sequence's plpgsql)

[scott.marlowe]

On Fri, 26 Sep 2003, Bruce Momjian wrote:

> Tom Lane wrote:
> > Peter Eisentraut <[EMAIL PROTECTED]> writes:
> > > Tom Lane writes:
> > >> so it appears that cygwin's "echo" generates a different newline style
> > >> than what got put into sql_features.txt.  A possible way to fix this is
> > >> to put the "\." line into sql_features.txt, but maybe there's a cleaner
> > >> answer.  Peter, any thoughts?
> > 
> > > There's no clean answer to this on Cygwin.  This specific case is just a
> > > little problem that we could solve locally, but in general you'll just end
> > > up annoying people if you require them to use consistent line endings on
> > > Cygwin.
> > 
> > Yeah, I was wondering whether you wouldn't propose dropping the newline
> > consistency check.  I'm not very comfortable with that, but maybe we
> > should.  Bruce?
> 
> I posted on that a few minutes ago.  Yea, we can drop it, but we risk
> eating carraige returns as data values.  I am not sure how consistently
> we output literal carriage returns in old dumps, nor how many apps
> produce on literal carriage returns in COPY. If we conditionally eat
> them, we run the risk of discarding some of their data without warning. 
> Perhaps we can throw a warning rather than an error, and adjust initdb
> to be consistent.

I'm running into issues where 7.4's pg_dump/pg_dumpall from a 7.2 database 
to a 7.4beta3 database is producing some errors like this:

ERROR:  literal newline found in data
HINT:  Use "\n" to represent newline.
CONTEXT:  COPY FROM, line 59

ERROR:  literal carriage return found in data
HINT:  Use "\r" to represent carriage return.
CONTEXT:  COPY FROM, line 41


These show up with little or no context, only the "line number" of the 
dump file.  Since I'm wrapping these up in pg_dumpall, I don't have the 
dump file so I don't know where the error is really occuring.  It would be 
nice to have such occurances echo the table / row they are getting the 
error on, or maybe just the first 20 or so characters, so they'd be easier 
to identify.

Is this related to this issue?


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

Re: [HACKERS] initdb failure (was Re: [GENERAL] sequence's plpgsql)

[Bruce Momjian]

Tom Lane wrote:
> Bruce Momjian <[EMAIL PROTECTED]> writes:
> > ... we could add code to throw the WARNING
> > only once per COPY command --- that would probably make sense.
> 
> Seems like a bit of a kluge, but perhaps the best compromise.  It would
> be quite likely that you'd get the same warning on many lines of a COPY,
> and that probably isn't really going to help people.

I can't think of any cases where we throw _tons_ of warnings at people,
like we could with COPY.

-- 
  Bruce Momjian|  http://candle.pha.pa.us
  [EMAIL PROTECTED]   |  (610) 359-1001
  +  If your life is a hard drive, |  13 Roberts Road
  +  Christ can be your backup.|  Newtown Square, Pennsylvania 19073

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

   http://archives.postgresql.org

Re: [HACKERS] initdb failure (was Re: [GENERAL] sequence's plpgsql)

[Peter Eisentraut]

Tom Lane writes:

> Yeah, I was wondering whether you wouldn't propose dropping the newline
> consistency check.  I'm not very comfortable with that, but maybe we
> should.  Bruce?

I don't mind if we keep it on pure-POSIX platforms.  But one of the nicer
developments on Windows in recent(?) times is that you can actually use
any kind of line separator and most programs still work correctly (with
the notable exception of Notepad).  The drawback of this is that you never
really know what kind of line separator a program is actually going to
write, especially when you start mixing Cygwin, MinGW, and native.  So
this newline consistency check is not only going to be pretty annoying on
Windows, it's going to cancel a feature of the operating system
environment.

-- 
Peter Eisentraut   [EMAIL PROTECTED]


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

Re: [HACKERS] initdb failure (was Re: [GENERAL] sequence's plpgsql)

[Tom Lane]

Bruce Momjian <[EMAIL PROTECTED]> writes:
> ... we could add code to throw the WARNING
> only once per COPY command --- that would probably make sense.

Seems like a bit of a kluge, but perhaps the best compromise.  It would
be quite likely that you'd get the same warning on many lines of a COPY,
and that probably isn't really going to help people.

regards, tom lane

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

Re: [HACKERS] 2-phase commit

[Marc G. Fournier]

On Fri, 26 Sep 2003, Tom Lane wrote:

> Bruce Momjian <[EMAIL PROTECTED]> writes:
> > Tom Lane wrote:
> >> You're not considering the possibility of a transient communication
> >> failure.
>
> > Can't the master re-send the request after a timeout?
>
> Not "it can", but "it has to".  The master *must* keep hold of that
> request forever (or until the slave responds, or until we reconfigure
> the system not to consider that slave valid anymore).  Similarly, the
> slave cannot forget the maybe-committed transaction on pain of not being
> a valid slave anymore.

Hr ... is there no way of having part of the protocol being a message
sent back that its a valid/invalid slave?  ie. slave has an uncommitted
transaction, never hears back from master to actually do the commit, so
after x-secs * y-retries any messages it does try to send to the master
have a bit flag set to 'invalid'?


---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

Re: [HACKERS] initdb failure (was Re: [GENERAL] sequence's plpgsql)

[Bruce Momjian]

Peter Eisentraut wrote:
> Tom Lane writes:
> 
> > so it appears that cygwin's "echo" generates a different newline style
> > than what got put into sql_features.txt.  A possible way to fix this is
> > to put the "\." line into sql_features.txt, but maybe there's a cleaner
> > answer.  Peter, any thoughts?
> 
> There's no clean answer to this on Cygwin.  This specific case is just a
> little problem that we could solve locally, but in general you'll just end
> up annoying people if you require them to use consistent line endings on
> Cygwin.

Here is a little diff to make initdb behave if we decide to keep the
COPY check.

-- 
  Bruce Momjian|  http://candle.pha.pa.us
  [EMAIL PROTECTED]   |  (610) 359-1001
  +  If your life is a hard drive, |  13 Roberts Road
  +  Christ can be your backup.|  Newtown Square, Pennsylvania 19073
Index: src/bin/initdb/initdb.sh
===
RCS file: /cvsroot/pgsql-server/src/bin/initdb/initdb.sh,v
retrieving revision 1.201
diff -c -c -r1.201 initdb.sh
*** src/bin/initdb/initdb.sh7 Sep 2003 03:36:03 -   1.201
--- src/bin/initdb/initdb.sh26 Sep 2003 18:23:16 -
***
*** 1087,1094 
echo "COPY information_schema.sql_features (feature_id, feature_name, 
sub_feature_id, sub_feature_name, is_supported, comments) FROM STDIN;"
cat "$datadir"/sql_features.txt
echo "\."
! ) \
!   | "$PGPATH"/postgres $PGSQL_OPT template1 > /dev/null || exit_nicely
  echo "ok"
  
  $ECHO_N "vacuuming database template1... "$ECHO_C
--- 1087,1095 
echo "COPY information_schema.sql_features (feature_id, feature_name, 
sub_feature_id, sub_feature_name, is_supported, comments) FROM STDIN;"
cat "$datadir"/sql_features.txt
echo "\."
! ) |
! tr -d '\r' | # make newlines consistent for Win32
! "$PGPATH"/postgres $PGSQL_OPT template1 > /dev/null || exit_nicely
  echo "ok"
  
  $ECHO_N "vacuuming database template1... "$ECHO_C

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

Re: [HACKERS] initdb failure (was Re: [GENERAL] sequence's plpgsql)

[Bruce Momjian]

Tom Lane wrote:
> Bruce Momjian <[EMAIL PROTECTED]> writes:
> > Perhaps we can throw a warning rather than an error, and adjust initdb
> > to be consistent.
> 
> I like the idea of reducing the newline consistency check to a warning.
> There is one thing we'd have to watch for though (it's already an issue
> but would become a bigger one): client-side COPY code had better be
> prepared to absorb backend Notice messages while processing COPY IN.
> Currently libpq doesn't read input data at all during a COPY IN loop,
> which means that if the COPY generates more than a few K of warning
> messages, the backend gets blocked on a full pipe and the whole
> operation locks up.  I have been meaning to fix that in libpq anyway,
> but what other client libraries might have the same issue?  Anyone know
> whether JDBC would need a similar fix?

Wow, that sounds big.  The ERROR will only happen once, while the
WARNING could happen a lot --- we could add code to throw the WARNING
only once per COPY command --- that would probably make sense.  I don't
see how we could get all clients to handle this for 7.4, particularly
clients from previous releases.

-- 
  Bruce Momjian|  http://candle.pha.pa.us
  [EMAIL PROTECTED]   |  (610) 359-1001
  +  If your life is a hard drive, |  13 Roberts Road
  +  Christ can be your backup.|  Newtown Square, Pennsylvania 19073

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

Re: [HACKERS] initdb failure (was Re: [GENERAL] sequence's plpgsql)

[Bruce Momjian]

Andrew Dunstan wrote:
> Bruce Momjian wrote:
> 
> >I posted on that a few minutes ago.  Yea, we can drop it, but we risk
> >eating carraige returns as data values.  I am not sure how consistently
> >we output literal carriage returns in old dumps, nor how many apps
> >produce on literal carriage returns in COPY. If we conditionally eat
> >them, we run the risk of discarding some of their data without warning. 
> >Perhaps we can throw a warning rather than an error, and adjust initdb
> >to be consistent.
> >
> >  
> >
> 
> Would the best longterm solution be to require escaping CR in a data 
> value? (Yes I know this might cause backwards compatibility problems, at 
> least for a while).

Yes, we do have that documented, but we supported it in earlier
releases, so we can't be sure who is using it, and it probably exists in
older dumps.

The particular problem is not literal carriage returns, which we don't
support any more, but carriage returns that happen to be at the end of
the line, right up against \n.  If we relax this, we will conditionally
strip off the \r.

In fact, we can do that now:

create table xx(text);

Right now, if all the column value end with \r, we will silently eat it.
But if some have \r and some do not, we will throw an error.  (Above,
when I say \r, I mean literal carriage return, not the "\r" string,
which we have always handled cleanly.

-- 
  Bruce Momjian|  http://candle.pha.pa.us
  [EMAIL PROTECTED]   |  (610) 359-1001
  +  If your life is a hard drive, |  13 Roberts Road
  +  Christ can be your backup.|  Newtown Square, Pennsylvania 19073

---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

Re: [HACKERS] initdb failure (was Re: [GENERAL] sequence's plpgsql)

[Tom Lane]

Bruce Momjian <[EMAIL PROTECTED]> writes:
> Perhaps we can throw a warning rather than an error, and adjust initdb
> to be consistent.

I like the idea of reducing the newline consistency check to a warning.
There is one thing we'd have to watch for though (it's already an issue
but would become a bigger one): client-side COPY code had better be
prepared to absorb backend Notice messages while processing COPY IN.
Currently libpq doesn't read input data at all during a COPY IN loop,
which means that if the COPY generates more than a few K of warning
messages, the backend gets blocked on a full pipe and the whole
operation locks up.  I have been meaning to fix that in libpq anyway,
but what other client libraries might have the same issue?  Anyone know
whether JDBC would need a similar fix?

regards, tom lane

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

Re: [HACKERS] initdb failure (was Re: [GENERAL] sequence's plpgsql)

[Andrew Dunstan]

Bruce Momjian wrote:

I posted on that a few minutes ago.  Yea, we can drop it, but we risk
eating carraige returns as data values.  I am not sure how consistently
we output literal carriage returns in old dumps, nor how many apps
produce on literal carriage returns in COPY. If we conditionally eat
them, we run the risk of discarding some of their data without warning. 
Perhaps we can throw a warning rather than an error, and adjust initdb
to be consistent.

 

Would the best longterm solution be to require escaping CR in a data 
value? (Yes I know this might cause backwards compatibility problems, at 
least for a while).

cheers

andrew

---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

Re: [HACKERS] initdb failure (was Re: [GENERAL] sequence's plpgsql)

[Bruce Momjian]

Tom Lane wrote:
> Peter Eisentraut <[EMAIL PROTECTED]> writes:
> > Tom Lane writes:
> >> so it appears that cygwin's "echo" generates a different newline style
> >> than what got put into sql_features.txt.  A possible way to fix this is
> >> to put the "\." line into sql_features.txt, but maybe there's a cleaner
> >> answer.  Peter, any thoughts?
> 
> > There's no clean answer to this on Cygwin.  This specific case is just a
> > little problem that we could solve locally, but in general you'll just end
> > up annoying people if you require them to use consistent line endings on
> > Cygwin.
> 
> Yeah, I was wondering whether you wouldn't propose dropping the newline
> consistency check.  I'm not very comfortable with that, but maybe we
> should.  Bruce?

I posted on that a few minutes ago.  Yea, we can drop it, but we risk
eating carraige returns as data values.  I am not sure how consistently
we output literal carriage returns in old dumps, nor how many apps
produce on literal carriage returns in COPY. If we conditionally eat
them, we run the risk of discarding some of their data without warning. 
Perhaps we can throw a warning rather than an error, and adjust initdb
to be consistent.

-- 
  Bruce Momjian|  http://candle.pha.pa.us
  [EMAIL PROTECTED]   |  (610) 359-1001
  +  If your life is a hard drive, |  13 Roberts Road
  +  Christ can be your backup.|  Newtown Square, Pennsylvania 19073

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

Re: [HACKERS] Threads vs Processes

[Dann Corbit]

> -Original Message-
> From: Tom Lane [mailto:[EMAIL PROTECTED] 
> Sent: Friday, September 26, 2003 9:27 AM
> To: Bruce Momjian
> Cc: Shridhar Daithankar; [EMAIL PROTECTED]; 
> [EMAIL PROTECTED]
> Subject: Re: [HACKERS] Threads vs Processes 
> 
> 
> Bruce Momjian <[EMAIL PROTECTED]> writes:
> > One solution is for me to continue with this in the Win32 
> CVS version 
> > until I have fork/exec() working on Unix, then test on 
> Win32.  I think 
> > that could be done in a few weeks, if not less.
> 
> > Another solution, already mentioned, is to use threads and 
> TLS.  This 
> > is what SRA's code uses.  I know SRA wants to contribute that code 
> > back to the community, so I can ask them to see if they are 
> ready to 
> > release it.
> 
> If you are willing to expend the effort, I think it would be 
> worth the time to pursue both approaches.  We don't yet have 
> enough info to decide which one will be cleaner, so we need 
> to push forward on both until we can make a realistic comparison.

I think the ideal situation would be a server that both threads and
forks.

Sometimes, we want the server to take on a new personality (with the
rights of the attaching user).  In such a case, threading is not a
sufficient answer.  Also, a forked process is a bit safer (though you
can put a try/catch around threads).

For performance with multiple queries from a single user, threads are
going to be faster than forking.  I think that the best model will be a
server that does both.

I know that there is some aversion to using C++, but the ACE framework
offers a consistent threading model that works for just about every
computer under the sun.
http://www.cs.wustl.edu/~schmidt/ACE.html

The license is basically BSD (it is called ACE, but it works exactly
like a BSD license).  With ACE, you program to a single API, and the
code works the same on every platform with a simple recompile.  It might
even be worthwhile to use the ACE higher level components to create a
server that supports multiple models of connection and threading.

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

[HACKERS] Metapa - PostgreSQL/Linux clustering for BI

[Ned Lilly]

Saw that these guys had raised some more VC money, and made an acquisition.  Anyone 
from the company on the lists?  (Probably best to reply only to -advocacy, but wanted 
to cast the net wide).

From their website (www.metapa.com):

--

Metapa CDB is an enterprise Java software application running on Linux. Key aspects of 
the CDB architecture include:

* Built on open standards.
* Based on a shared-nothing architecture.
* Advanced SQL parallelization engine supports predicate-based partitioning and query 
acceleration.
* Based on a database-agnostic framework allowing clustering capability for different 
RDBMs, initially supporting PostgreSQL.
* Standards based client interface (JDBC/ODBC/DBI).
* Custom low-latency, preemptable protocol on top of TCP.
* Purpose-built from the ground up to increase the performance of "star-schema" data 
models typical in today's demanding decision support systems.

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

Re: [HACKERS] 2-phase commit

[Bruce Momjian]

Tom Lane wrote:
> Bruce Momjian <[EMAIL PROTECTED]> writes:
> > Tom Lane wrote:
> >> You're not considering the possibility of a transient communication
> >> failure.
> 
> > Can't the master re-send the request after a timeout?
> 
> Not "it can", but "it has to".  The master *must* keep hold of that
> request forever (or until the slave responds, or until we reconfigure
> the system not to consider that slave valid anymore).  Similarly, the
> slave cannot forget the maybe-committed transaction on pain of not being
> a valid slave anymore.  You can make this work, but the resource costs
> are steep.  For instance, in Postgres, you don't get to truncate the WAL
> log, for what could be a really really long time --- more disk space
> than you wanted to spend on WAL anyway.  The locks held by the
> maybe-committed transaction are another potentially unpleasant problem;
> you can't release them, no matter what else they are blocking.

I think we would need a configurable timeout to say a slave is no longer
valid, like 60 seconds, and then let everyone release.  We can let the
administrator decide how long he wants to try to keep two hosts
communicating.  I don't see this as much different from multi-master
replication problems.

-- 
  Bruce Momjian|  http://candle.pha.pa.us
  [EMAIL PROTECTED]   |  (610) 359-1001
  +  If your life is a hard drive, |  13 Roberts Road
  +  Christ can be your backup.|  Newtown Square, Pennsylvania 19073

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

Re: [HACKERS] initdb failure (was Re: [GENERAL] sequence's plpgsql)

[Tom Lane]

Peter Eisentraut <[EMAIL PROTECTED]> writes:
> Tom Lane writes:
>> so it appears that cygwin's "echo" generates a different newline style
>> than what got put into sql_features.txt.  A possible way to fix this is
>> to put the "\." line into sql_features.txt, but maybe there's a cleaner
>> answer.  Peter, any thoughts?

> There's no clean answer to this on Cygwin.  This specific case is just a
> little problem that we could solve locally, but in general you'll just end
> up annoying people if you require them to use consistent line endings on
> Cygwin.

Yeah, I was wondering whether you wouldn't propose dropping the newline
consistency check.  I'm not very comfortable with that, but maybe we
should.  Bruce?

regards, tom lane

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

Re: [HACKERS] initdb failure (was Re: [GENERAL] sequence's plpgsql)

[Bruce Momjian]

Peter Eisentraut wrote:
> Tom Lane writes:
> 
> > so it appears that cygwin's "echo" generates a different newline style
> > than what got put into sql_features.txt.  A possible way to fix this is
> > to put the "\." line into sql_features.txt, but maybe there's a cleaner
> > answer.  Peter, any thoughts?
> 
> There's no clean answer to this on Cygwin.  This specific case is just a
> little problem that we could solve locally, but in general you'll just end
> up annoying people if you require them to use consistent line endings on
> Cygwin.

This error is coming from the new 7.4 COPY code that allows \r\n as a
line terminator.  Requiring the end-of-line to be consistent seemed to
be the only way to be sure we were not eating a literal carriage return
in the data stream.  Let's put the "\." into sql_features.txt and see if
that fixes it, or can we use echo -c "\.\n" in initdb?

-- 
  Bruce Momjian|  http://candle.pha.pa.us
  [EMAIL PROTECTED]   |  (610) 359-1001
  +  If your life is a hard drive, |  13 Roberts Road
  +  Christ can be your backup.|  Newtown Square, Pennsylvania 19073

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

   http://www.postgresql.org/docs/faqs/FAQ.html

Re: [HACKERS] 2-phase commit

[Tom Lane]

Bruce Momjian <[EMAIL PROTECTED]> writes:
> Tom Lane wrote:
>> You're not considering the possibility of a transient communication
>> failure.

> Can't the master re-send the request after a timeout?

Not "it can", but "it has to".  The master *must* keep hold of that
request forever (or until the slave responds, or until we reconfigure
the system not to consider that slave valid anymore).  Similarly, the
slave cannot forget the maybe-committed transaction on pain of not being
a valid slave anymore.  You can make this work, but the resource costs
are steep.  For instance, in Postgres, you don't get to truncate the WAL
log, for what could be a really really long time --- more disk space
than you wanted to spend on WAL anyway.  The locks held by the
maybe-committed transaction are another potentially unpleasant problem;
you can't release them, no matter what else they are blocking.

regards, tom lane

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

   http://www.postgresql.org/docs/faqs/FAQ.html

Re: [HACKERS] initdb failure (was Re: [GENERAL] sequence's plpgsql)

[Peter Eisentraut]

Tom Lane writes:

> so it appears that cygwin's "echo" generates a different newline style
> than what got put into sql_features.txt.  A possible way to fix this is
> to put the "\." line into sql_features.txt, but maybe there's a cleaner
> answer.  Peter, any thoughts?

There's no clean answer to this on Cygwin.  This specific case is just a
little problem that we could solve locally, but in general you'll just end
up annoying people if you require them to use consistent line endings on
Cygwin.

-- 
Peter Eisentraut   [EMAIL PROTECTED]


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

   http://www.postgresql.org/docs/faqs/FAQ.html

Re: [HACKERS] 2-phase commit

[Bruce Momjian]

Patrick Welche wrote:
> On Fri, Sep 26, 2003 at 02:49:30PM -0300, Marc G. Fournier wrote:
> ... 
> > if we are talking two computers sitting next to each other on a switch,
> > you'd expect those to be low ... but if you were talking about two
> > seperate geographical locations (and yes, I realize you are adding lag to
> > the mix with waiting for responses), you'd expect those #s to rise ...
> 
> Which I thought was the whole point of using a group communication protocol
> such as spread in postgresql-r. It seemed solved there...

Right, but I think we want to try to do two-phase commit without spread.
Spread seems overkill for this usage.

-- 
  Bruce Momjian|  http://candle.pha.pa.us
  [EMAIL PROTECTED]   |  (610) 359-1001
  +  If your life is a hard drive, |  13 Roberts Road
  +  Christ can be your backup.|  Newtown Square, Pennsylvania 19073

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

Re: [HACKERS] 2-phase commit

[Patrick Welche]

On Fri, Sep 26, 2003 at 02:49:30PM -0300, Marc G. Fournier wrote:
... 
> if we are talking two computers sitting next to each other on a switch,
> you'd expect those to be low ... but if you were talking about two
> seperate geographical locations (and yes, I realize you are adding lag to
> the mix with waiting for responses), you'd expect those #s to rise ...

Which I thought was the whole point of using a group communication protocol
such as spread in postgresql-r. It seemed solved there...

Cheers,

Patrick

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

   http://www.postgresql.org/docs/faqs/FAQ.html

Re: [HACKERS] 2-phase commit

[Marc G. Fournier]

On Fri, 26 Sep 2003, Tom Lane wrote:

> Bruce Momjian <[EMAIL PROTECTED]> writes:
> > Could we allow slaves to check if the backend is still alive, perhaps by
> > asking the postmaster, similar to what we do with the cancel signal ---
> > that way, the slave would never time out and always wait if the master
> > was alive.
>
> You're not considering the possibility of a transient communication
> failure.  The fact that you cannot currently contact the other guy
> is not proof that he's not still alive.
>
> Example:
>
>   Master  Slave
>   --  -
>   commit ready-->
>   <--OK
>   commit done->XX
>
> where "->XX" means the message gets lost due to network failure.  Now
> what?

'k, but isn't alot of that a "retry" issue?  we're talking TCP here, not
UDP, which I *thought* was designed for transient network problems ... ?
I would think that any implementation would have a timeout/retry GUC
variable associated with it ... 'if no answer in x seconds, retry up to y
times' ...

if we are talking two computers sitting next to each other on a switch,
you'd expect those to be low ... but if you were talking about two
seperate geographical locations (and yes, I realize you are adding lag to
the mix with waiting for responses), you'd expect those #s to rise ...


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

   http://www.postgresql.org/docs/faqs/FAQ.html

Re: [HACKERS] 2-phase commit

[Marc G. Fournier]

On Fri, 26 Sep 2003, Tom Lane wrote:

> Bruce Momjian <[EMAIL PROTECTED]> writes:
> > Could we allow slaves to check if the backend is still alive, perhaps by
> > asking the postmaster, similar to what we do with the cancel signal ---
> > that way, the slave would never time out and always wait if the master
> > was alive.
>
> You're not considering the possibility of a transient communication
> failure.  The fact that you cannot currently contact the other guy
> is not proof that he's not still alive.
>
> Example:
>
>   Master  Slave
>   --  -
>   commit ready-->
>   <--OK
>   commit done->XX
>
> where "->XX" means the message gets lost due to network failure.  Now

'k, but isn't alot of that a "retry" issue?  we're talking TCP here, not
UDP, which I *thought* was designed for transient network problems ... ?
I would think that any implementation would have a timeout/retry GUC
variable associated with it ... 'if no answer in x seconds, retry up to y
times' ...

if we are talking two computers sitting next to each other on a switch,
you'd expect those to be low ... but if you were talking about two
seperate geographical locations (and yes, I realize you are adding lag to
the mix with waiting for responses), you'd expect those #s to rise ...



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

Re: [HACKERS] 2-phase commit

[Bruce Momjian]

Tom Lane wrote:
> Bruce Momjian <[EMAIL PROTECTED]> writes:
> > Could we allow slaves to check if the backend is still alive, perhaps by
> > asking the postmaster, similar to what we do with the cancel signal ---
> > that way, the slave would never time out and always wait if the master
> > was alive.
> 
> You're not considering the possibility of a transient communication
> failure.  The fact that you cannot currently contact the other guy
> is not proof that he's not still alive.
> 
> Example:
> 
>   Master  Slave
>   --  -
>   commit ready-->
>   <--OK
>   commit done->XX
> 
> where "->XX" means the message gets lost due to network failure.  Now
> what?  The slave cannot abort; he promised he could commit, and he does
> not know whether the master has committed or not.  The master does not
> know the slave's state either; maybe he got the second message, and
> maybe he didn't.  Both sides are forced to keep information about the 
> open transaction indefinitely.  Timing out on either side could yield
> the wrong result.

Can't the master re-send the request after a timeout?

-- 
  Bruce Momjian|  http://candle.pha.pa.us
  [EMAIL PROTECTED]   |  (610) 359-1001
  +  If your life is a hard drive, |  13 Roberts Road
  +  Christ can be your backup.|  Newtown Square, Pennsylvania 19073

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

Re: [HACKERS] Error message cleanup

[Peter Eisentraut]

Alvaro Herrera writes:

> Regarding your message cleanup, I wonder about:
>
> #: rewrite/rewriteDefine.c:274
> msgid "rules on SELECT rule must have action INSTEAD SELECT"
>
> Is this really the intended wording?
>
> #: utils/misc/guc.c:1318
> msgid "log statement generating error at or above this level"
>
> Should this be "log statements", plural?

Both of these were mistakes.  Thanks for pointing it out.

-- 
Peter Eisentraut   [EMAIL PROTECTED]


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

Re: [HACKERS] 2-phase commit

[Tom Lane]

Bruce Momjian <[EMAIL PROTECTED]> writes:
> Could we allow slaves to check if the backend is still alive, perhaps by
> asking the postmaster, similar to what we do with the cancel signal ---
> that way, the slave would never time out and always wait if the master
> was alive.

You're not considering the possibility of a transient communication
failure.  The fact that you cannot currently contact the other guy
is not proof that he's not still alive.

Example:

Master  Slave
--  -
commit ready-->
<--OK
commit done->XX

where "->XX" means the message gets lost due to network failure.  Now
what?  The slave cannot abort; he promised he could commit, and he does
not know whether the master has committed or not.  The master does not
know the slave's state either; maybe he got the second message, and
maybe he didn't.  Both sides are forced to keep information about the 
open transaction indefinitely.  Timing out on either side could yield
the wrong result.

regards, tom lane

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

Re: [HACKERS] 2-phase commit

[Bruce Momjian]

Zeugswetter Andreas SB SD wrote:
> 
> > > From our previous discussion of 2-phase commit, there was concern that
> > > the failure modes of 2-phase commit were not solvable.  However, I think
> > > multi-master replication is going to have similar non-solvable failure
> > > modes, yet people still want multi-master replication.
> > 
> > No.  The real problem with 2PC in my mind is that its failure modes
> > occur *after* you have promised commit to one or more parties.  In
> > multi-master, if you fail you know it before you have told the client
> > his data is committed.
> 
> Hmm ? The appl cannot take the first phase commit as its commit info. It 
> needs to wait for the second phase commit. The second phase is only finished
> when all coservers have reported back. 2PC is synchronous.
> 
> The problems with 2PC are when after second phase commit was sent to all
> servers and before all report back one of them becomes unreachable/down ...
> (did it receive and do the 2nd commit or not) Such a transaction must stay
> open until the coserver is reachable again or an administrator committed/aborted it. 
> 
> It is multi master replication that usually has an asynchronous mode for
> performance, and there the trouble starts.

Let me diagram this so we can see the issues.  Normal operation is:

Master  Slave
--  -
commit ready-->
<--OK
commit done--->
<--OK
completed

One possible failure is:

Master  Slave
--  -
commit ready-->
<--OK
commit done--->
dies here
stuck waiting

Another possible failure is:

Master  Slave
--  -
commit ready-->
<--OK
dies here
stuck waiting

Are these the issues?  Can't we just add GUC timeouts to cause the
commit to fail, and the slave to stop waiting?  I suppose a problem is:

Master  Slave
--  -
commit ready-->
<--OK
sleep
stuck waiting, times out
commit done

Could we allow slaves to check if the backend is still alive, perhaps by
asking the postmaster, similar to what we do with the cancel signal ---
that way, the slave would never time out and always wait if the master
was alive.

-- 
  Bruce Momjian|  http://candle.pha.pa.us
  [EMAIL PROTECTED]   |  (610) 359-1001
  +  If your life is a hard drive, |  13 Roberts Road
  +  Christ can be your backup.|  Newtown Square, Pennsylvania 19073

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

Re: [HACKERS] invalid tid errors in latest 7.3.4 stable.

[Tom Lane]

Stephan Szabo <[EMAIL PROTECTED]> writes:
> I think theoretically in serializable the cases where the difference
> between the snapshot from this statement and the standard snapshot for the
> transaction are noticable we probably have a serialization failure

Hmm, that is a good point.  It would be cleaner to throw a "can't
serialize" failure than have the RI triggers run under a different
snapshot.  I am not sure if we can implement that behavior easily,
though.  Can you think of a way to detect whether there's an RI conflict
against a later-started transaction?

regards, tom lane

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

   http://archives.postgresql.org

Re: [HACKERS] invalid tid errors in latest 7.3.4 stable.

[Stephan Szabo]

On Fri, 26 Sep 2003, Tom Lane wrote:

> >> Okay, I'll work out some extension of the APIs to let us propagate the
> >> snapshot request down through SPI and into the Executor, rather than
> >> using a global variable for it.  (Unless someone has a better idea...)
>
> Just when you thought it was safe to go back in the water ...
>
> Chris Kratz sent me the attached example, which fails in 7.3 and (still)
> fails in CVS HEAD too.

> As far as the "DELETE FROM qry_column_list" goes, I think the solution
> is that fetching rows can't use pure SnapshotNow after all.  What we
> need is to create a fresh QuerySnapshot that shows all transactions
> committed-to-date as committed, and saves the current CommandCounter as
> the criterion for locally created rows.  Unlike SnapshotNow, this would
> mean that transactions committed just after we take the new snapshot
> would not be seen as committed.  This should be okay AFAICS --- once we
> reach the RI triggers, all transactions we need to worry about should be
> committed.  (If not, surely there's a race condition anyway.)  Also note

That should be true. By the time the triggers have started running,
anything that might be conflicting shouldn't be able to commit until after
the transaction the trigger is in (since we already have locks on rows
they'd need to be able to lock).

> that an RI query would *not* see the effects of actions it indirectly
> triggers.  This should be okay, because if they do anything that
> requires RI validation, they should cause additional RI trigger firings
> to be queued for attention later.

I feel vague uneasiness about this, but can't think of a counter example,
it's probably just breakfast acting up.

> But Chris' example raises a host of other questions in my mind.  Should
> we apply this forcibly-updated QuerySnapshot to actions that are
> indirectly triggered by RI queries?  In CVS tip, SnapshotNow rules are
> in fact used for the UPDATE that's generated by the RULE, because it's
> part of the generated plan for the DELETE.  But any queries executed
> inside triggers fired as a result of all this would use the pre-existing
> QuerySnapshot, and hence could see a worldview completely inconsistent
> with the rows they are being fired for :-(.  It's worse in 7.3, because
> the first trigger exit would revert ReferentialIntegritySnapshotOverride
> to false, meaning you wouldn't even be using the same snapshot rules
> throughout the UPDATE/DELETE :-( :-(
>
> I am inclined to think now that the right solution is for the RI
> triggers to update the global QuerySnapshot to current time when they
> start, and then revert it to what it had been before exiting.  (And that
> code had better be in the RI triggers themselves, *not* in the generic
> trigger-calling code.)  This would ensure that actions taken indirectly
> as a result of RI behavior would see a consistent worldview.
>
> The main argument I can see against this is that it would be a really
> big wart on the behavior of SERIALIZABLE transactions.  Instead of
> saying "in a SERIALIZABLE transaction, you only see the effects of
> transactions committed before your transaction started", we'd have to
> add a footnote "except in actions taken as a result of RI-generated
> queries", which sure complicates matters from a logical point of view.
> (In READ COMMITTED mode, on the other hand, it's no big deal; we are
> effectively just decreeing that a new command starts before the RI
> triggers run.)

Given the two suggestions above, I think I'd vote for the latter. It seems
to break the idea of serializable, but it seems like it'd be easier for
people to work with than the former where you might not be able to even
see the row you're working upon in its own triggers.

I think theoretically in serializable the cases where the difference
between the snapshot from this statement and the standard snapshot for the
transaction are noticable we probably have a serialization failure since
I think that case comes in when a transaction that started after us (and
has already committed) has done something that the constraint's search
condition would need to see we'd potentially be opening up the possibility
for phantoms. If we have to get the same rows as a previous set for
the same search condition, but the row in question was already committed
by that transaction that started after this one, we no longer have the
freedom to pretend that the transactions were in the other order.

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

Re: [HACKERS] Threads vs Processes

[Bruce Momjian]

Tom Lane wrote:
> Bruce Momjian <[EMAIL PROTECTED]> writes:
> > One solution is for me to continue with this in the Win32 CVS version
> > until I have fork/exec() working on Unix, then test on Win32.  I think
> > that could be done in a few weeks, if not less.
> 
> > Another solution, already mentioned, is to use threads and TLS.  This is
> > what SRA's code uses.  I know SRA wants to contribute that code back to
> > the community, so I can ask them to see if they are ready to release it.
> 
> If you are willing to expend the effort, I think it would be worth the
> time to pursue both approaches.  We don't yet have enough info to decide
> which one will be cleaner, so we need to push forward on both until we
> can make a realistic comparison.

I think I know enough to get the fork/exec working, particularly because
most of the work can be tested under Unix.  I don't know enough to get
the threads working, and I have had no offers of help since I started.

-- 
  Bruce Momjian|  http://candle.pha.pa.us
  [EMAIL PROTECTED]   |  (610) 359-1001
  +  If your life is a hard drive, |  13 Roberts Road
  +  Christ can be your backup.|  Newtown Square, Pennsylvania 19073

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

   http://archives.postgresql.org

Re: [HACKERS] Threads vs Processes

[Tom Lane]

Bruce Momjian <[EMAIL PROTECTED]> writes:
> One solution is for me to continue with this in the Win32 CVS version
> until I have fork/exec() working on Unix, then test on Win32.  I think
> that could be done in a few weeks, if not less.

> Another solution, already mentioned, is to use threads and TLS.  This is
> what SRA's code uses.  I know SRA wants to contribute that code back to
> the community, so I can ask them to see if they are ready to release it.

If you are willing to expend the effort, I think it would be worth the
time to pursue both approaches.  We don't yet have enough info to decide
which one will be cleaner, so we need to push forward on both until we
can make a realistic comparison.

regards, tom lane

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

Re: [HACKERS] pg_dump and REVOKE on function

[Bruce Momjian]

This item has been added to the 7.4 open items list:

ftp://momjian.postgresql.org/pub/postgresql/open_items

---

Rod Taylor wrote:
-- Start of PGP signed section.
> Below is output from 7.3 pg_dump that is being loaded into 7.4 beta1.
> 
> It would seem that revoking the permissions of the owner doesn't work
> out so well.
> 
> 
> r=# CREATE FUNCTION weekdate (date) RETURNS timestamp with time zone
> r-# AS '
> r'# SELECT cast(to_date(''01 01 ''|| extract(''year'' FROM $1), ''DD MM
> '') +
> r'# (cast(extract(''week'' FROM $1) AS numeric) *7-8) * interval ''1
> day'' as timestamp with time zone);'
> r-# LANGUAGE sql;
> CREATE FUNCTION
> r=#
> r=#
> r=# --
> r=# -- TOC entry 752 (OID 18968885)
> r=# -- Name: weekdate (date); Type: ACL; Schema: public; Owner: rbt
> r=# --
> r=#
> r=# REVOKE ALL ON FUNCTION weekdate (date) FROM PUBLIC;
> REVOKE
> r=# GRANT ALL ON FUNCTION weekdate (date) TO PUBLIC;
> GRANT
> r=# REVOKE ALL ON FUNCTION weekdate (date) FROM rbt;
> ERROR:  dependent privileges exist
> HINT:  Use CASCADE to revoke them too.
> 
-- End of PGP section, PGP failed!

-- 
  Bruce Momjian|  http://candle.pha.pa.us
  [EMAIL PROTECTED]   |  (610) 359-1001
  +  If your life is a hard drive, |  13 Roberts Road
  +  Christ can be your backup.|  Newtown Square, Pennsylvania 19073

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

Re: [HACKERS] Threads vs Processes

[Bruce Momjian]

Tom Lane wrote:
> Shridhar Daithankar <[EMAIL PROTECTED]> writes:
> > We really don't need threads to replace existing functionality. That
> > would be dog work.
> 
> No, that's not the point at all.  The problem we are facing at the
> moment with the Windows port is lack of fork(), which means there's
> no way for separate-subprocess backends to inherit variable values
> from the postmaster.  Bruce has been trying to fix that by having the
> subprocesses somehow reload or re-deduce all those variables; which
> is messy, bug-prone, and probably race-condition-prone too.  In a
> threaded implementation it would maybe be relatively easy to initialize
> a new thread's TLS by copying the postmaster thread's TLS, in which case
> a whole pile of as-yet-unwritten Windows-only code won't be needed.

I haven't said much in this thread yet because I wasn't sure what to
say.

I worked 2-3 weeks on Win32 back in March/April, and a few days last
month getting it to compile again, and for the interfaces/clients to run
under Win32.

I haven't had any time to work on the fork/exec, though I now have a few
weeks to spend on it, so it isn't that I have been trying to get
fork/exec working, and failing, it is that I haven't even tried lately.

My plan is to pass a few values to the child via the command line:

#ifdef EXEC_BACKEND
Assert(UsedShmemSegID != 0 && UsedShmemSegAddr != NULL);
/* database name at the end because it might contain commas */
snprintf(pbuf, NAMEDATALEN + 256, "%d,%d,%d,%p,%s", port->sock, 
canAcceptConnections(),
 UsedShmemSegID, UsedShmemSegAddr, port->database_name);
av[ac++] = pbuf;
#else

and pass the GUC settings via a special binary file.  (Those are already
in main CVS.)  The other values I plan to regenerate in the child the
same way the postmaster does it at initialization time.  The easy part
of that is that I only have to worry about postmaster variables.  All my
current fork/exec work is marked by #ifdef EXEC_BACKEND in current CVS,
so it can be easily ripped out.

One solution is for me to continue with this in the Win32 CVS version
until I have fork/exec() working on Unix, then test on Win32.  I think
that could be done in a few weeks, if not less.

Another solution, already mentioned, is to use threads and TLS.  This is
what SRA's code uses.  I know SRA wants to contribute that code back to
the community, so I can ask them to see if they are ready to release it.
That would show us all the changes needed to do threading.  Their code
is based on 7.3.X, rather than PeerDirect's which is based on 7.2.

-- 
  Bruce Momjian|  http://candle.pha.pa.us
  [EMAIL PROTECTED]   |  (610) 359-1001
  +  If your life is a hard drive, |  13 Roberts Road
  +  Christ can be your backup.|  Newtown Square, Pennsylvania 19073

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

Re: [HACKERS] [ADMIN] postgres 6.2 vacuum

[Lamar Owen]

On Friday 26 September 2003 10:52, Tom Lane wrote:
> Lamar Owen <[EMAIL PROTECTED]> writes:
> > This isn't necessarily true.  That old of a version of PostgreSQL is
> > probably running on a quite out-of-date OS -- for instance, if the OS was
> > Red Hat Linux, then the point at which 6.2.1 was shipped was RHL 5.0. 
> > Can you even compile PostgreSQL 7.3.x on RHL 5.0 or its contemporaries?

> Surely.  We still support other platforms that make RHL 5.0 look like
> the new kid on the block.  There might not be RPMs available, but I
> can't believe it wouldn't compile from source.

I think I tried a 7.1.x on 5.2 a long time ago, and it didn't build for some 
reason.  But that has been some time ago.  I might just build up a 5.2 system 
(plus errata) to see.

> I do agree that people running that old a Linux distro need to think
> about updating more than just Postgres, though.  They have kernel bugs
> as well as PG bugs to fear :-(

2.0 happily doesn't have many new bugs, and it is being maintained, IIRC.  
Just not by Red Hat.
-- 
Lamar Owen
Director of Information Technology
Pisgah Astronomical Research Institute
1 PARI Drive
Rosman, NC  28772
(828)862-5554
www.pari.edu

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

Re: [HACKERS] initdb failure (was Re: [GENERAL] sequence's plpgsql)

[Michael Meskes]

On Fri, Sep 26, 2003 at 11:01:34AM -0400, Tom Lane wrote:
> > I am getting the error:
> > "
> > creating information schema... ERROR:  end-of-copy marker does not match 
> > previous newline style
> > CONTEXT:  COPY FROM, line 361
> > "
> 
> That's interesting.  COPY is complaining because the \. terminator in
> the file it's been fed has a different kind of newline after it than
> the newlines earlier in the file (LF vs CR/LF, no doubt).  The part

Well actually I'm not so sure. I tried installing 7.4 from Oliver's
Debian packages and experienced exactly the same. We have yet to find
out why, but I doubt newlines changed between his Debian system and mine.

> of the initdb script that must be causing this is
> 
>   echo "COPY information_schema.sql_features (feature_id, feature_name, 
> sub_feature_id, sub_feature_name, is_supported, comments) FROM STDIN;"
>   cat "$datadir"/sql_features.txt
>   echo "\."
> ) \
>   | "$PGPATH"/postgres $PGSQL_OPT template1 > /dev/null || exit_nicely
> echo "ok"
> 
> so it appears that cygwin's "echo" generates a different newline style
> than what got put into sql_features.txt.  A possible way to fix this is
> to put the "\." line into sql_features.txt, but maybe there's a cleaner
> answer.  Peter, any thoughts?

Putting the "\." line into sql_features.txt did help me, too. 

Michael
-- 
Michael Meskes
Email: Michael at Fam-Meskes dot De
ICQ: 179140304, AIM/Yahoo: michaelmeskes, Jabber: [EMAIL PROTECTED]
Go SF 49ers! Go Rhein Fire! Use Debian GNU/Linux! Use PostgreSQL!

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

Re: [pgsql-hackers-win32] [HACKERS] Threads vs Processes

[Andreas Pflug]

Tom Lane wrote:

"Merlin Moncure" <[EMAIL PROTECTED]> writes:
 

Tom Lane wrote:
   

Surely the addresses can be assumed constant within a thread.
Otherwise we have a problem here too.
 

 

Quoting from the MSDN:
The address of a thread local object is not considered constant, and any
expression involving such an address is not considered a constant
expression.
   

Ah.  That's probably reasonable.  Still a bit of a PITA for us, as there
are various places that do give a static variable an initializer
pointing to another static.  But that could be worked around I think.
I thought you were saying that the compiler would forbid taking a TLS
variable's address even at runtime.
 

Tom,

you wrote you wouldn't like the idea of a struct representing the now 
global variables for a thread, because this would mean that every module 
would need to access it, and any change of a module-local variable would 
affect the complete backend.

This could be worked around:
If that global struct is just a list of pointers to memory blocks, each 
block representing the opaque local data of a module, this can be 
avoided. This could be duplicated easily for thread creation, if zwo 
ints representing mem block size and size to copy for each block is 
included. Additional pointers for creation and cleanup functions could 
help for more special initializations.

This would make the thing independent of fancy compiler features and 
platforms.

Regards,
Andreas


---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

Re: [HACKERS] invalid tid errors in latest 7.3.4 stable.

[Tom Lane]

Shridhar Daithankar <[EMAIL PROTECTED]> writes:
> If the trigger function is precompiled, the error would not be reproducible 
> and it will work correctly, right?

Only because the trigger in the example doesn't issue any queries of its
own.  If it did, it would cause CommandCounterIncrement(s) anyway.

> Can we precompile such RI triggers on postmaster startup? Could that be a 
> workaround?

I've thought for some time that it's a bad idea that there is an extra
CCI done when compiling a plpgsql function, because it creates
inconsistencies of behavior.  But getting rid of it does not fix the
fundamental issues here at all, it merely means that this particular
drastically-oversimplified example wouldn't happen to fail.

(IIRC, the extra CCI is actually in spi.c, not in plpgsql, so removing
it could potentially break other code; thus I've hesitated to do it.)

regards, tom lane

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

Re: [HACKERS] feature request: show pgsql version when running initdb

[Andrew Dunstan]

Tom Lane wrote:

"Nigel J. Andrews" <[EMAIL PROTECTED]> writes:
 

Moral of the story, if it's in your path first then it's the default and you
should therefore be happy with the results or be prepared to live with them,
otherwise make sure what you're running.
   

I would think that having initdb print its version while running would
be in the category of "too late to help" anyway.  Making a habit of
running "initdb --version" in advance seems like much the better way
to proceed, if this is a problem for you.
Personally I've never had such a mistake occur, even though I normally
have several different PG installations active on my development
machines.  I think this is because I always set PATH, PGPORT, and PGDATA
consistently --- rather than manipulating them by hand, I have little
scripts that I run to make one installation or another the "active" one.
 

It might make sense to have the version explicitly reported by 
show_setting/debug modes - the change would be entirely trivial and not 
very intrusive to general use.

cheers

andrew

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

Re: [HACKERS] initdb failure (was Re: [GENERAL] sequence's plpgsql)

[Matthew T. O'Connor]

On Fri, 2003-09-26 at 11:01, Tom Lane wrote:
> so it appears that cygwin's "echo" generates a different newline style
> than what got put into sql_features.txt.  A possible way to fix this is
> to put the "\." line into sql_features.txt, but maybe there's a cleaner
> answer.  Peter, any thoughts?

Does cygwin still have the install time option of what type of line feed
to use?  I know at one point (a long time ago) when I installed cygwin,
and chose windows line feeds (CRLF) that it caused problems with several
applications.  So the problem might be that with CYGWIN you could have
either type of line feed depending on what the user selected during
install.


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

   http://www.postgresql.org/docs/faqs/FAQ.html

[HACKERS] initdb failure (was Re: [GENERAL] sequence's plpgsql)

[Tom Lane]

Tim McAuley <[EMAIL PROTECTED]> writes:
> Another question now. I am unable to compile Postgresql 7.4 beta 3 under 
> cygwin (Windows 2K, using cgyipc 2).

> I am getting the error:
> "
> creating information schema... ERROR:  end-of-copy marker does not match 
> previous newline style
> CONTEXT:  COPY FROM, line 361
> "

That's interesting.  COPY is complaining because the \. terminator in
the file it's been fed has a different kind of newline after it than
the newlines earlier in the file (LF vs CR/LF, no doubt).  The part
of the initdb script that must be causing this is

  echo "COPY information_schema.sql_features (feature_id, feature_name, 
sub_feature_id, sub_feature_name, is_supported, comments) FROM STDIN;"
  cat "$datadir"/sql_features.txt
  echo "\."
) \
| "$PGPATH"/postgres $PGSQL_OPT template1 > /dev/null || exit_nicely
echo "ok"

so it appears that cygwin's "echo" generates a different newline style
than what got put into sql_features.txt.  A possible way to fix this is
to put the "\." line into sql_features.txt, but maybe there's a cleaner
answer.  Peter, any thoughts?

regards, tom lane

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