Re: [HACKERS] Postgres 9.6 Logical and Fisical replication

[Mario Fernando Guerrero Díaz]

Thank you for the clarification.

El 5/10/2017 9:27 AM, "Robert Haas"  escribió:

> On Mon, Sep 18, 2017 at 5:30 PM, guedim  wrote:
> > I am working with Postgres9.6 with a Master/Slave cluster replication
> using
> > Streaming replication.
> > I would like to add a new Slave server database but this database with
> > logical replication .
> >
> > I tried with some configurations but it was not possible  :(
> >
> > https://github.com/guedim/postgres-streaming-replication
> >
> > Here is the image of what is in my mind:
> >  >
>
> This question is really off-topic for this list, which is probably why
> you haven't gotten any replies.  This list is for discussion of
> PostgreSQL development; there are other lists for user questions, like
> pgsql-general.  Logical replication is only supported beginning in
> PostgreSQL 10; if you are using some earlier version, you need an
> add-on tool like pglogical, slony, etc.
>
> Please also read https://wiki.postgresql.org/wiki/Guide_to_reporting_
> problems
>
> --
> Robert Haas
> EnterpriseDB: http://www.enterprisedb.com
> The Enterprise PostgreSQL Company
>

Re: [HACKERS] signal handling in plpython

[Mario De Frutos Dieguez]

Hi!

Following your ideas I've made a test [here
<https://github.com/CartoDB/postgres/commit/d587d8a6e4f035cc45e1d84fc46aa7c3ab0344c3>],
only in plpython and seems to works pretty well. I've to make more tests
and execute the postgres regress too.

This ad-hoc solution could be enough for now, we don't have
shared_preload_libraries
as Heikki pointed, because in the next week we need to be able to interrupt
plpython functions.

But, I would REALLY LOVE to implement a proper solution for this case,
making hooks in Postgres for extensions like Heikki propose or any other
proposal. I'm really enjoying to work in the Postgres internals
and at least I'd like to finish this PATCH.

Any suggestion on what do I need to read, similar cases, advices, etc?

Again thank you very much for your time and you invaluable help.

Mario



2016-10-14 15:22 GMT+02:00 Tom Lane <t...@sss.pgh.pa.us>:

> Heikki Linnakangas <hlinn...@iki.fi> writes:
> > On 10/14/2016 04:05 PM, Tom Lane wrote:
> >> I wrote:
> >>> Py_AddPendingCall is safe to call from a signal handler?  That would
> >>> be ... quite remarkable.
>
> > Yes, I believe it is.
>
> > https://github.com/python/cpython/blob/4b71e63b0616aa2a44c9b13675e4c8
> e3c0157481/Python/ceval.c#L422
>
> I don't know whether to laugh or cry, but that code is a joke.  Just
> silently fail if you can't get the lock?
>
> regards, tom lane
>

Re: [HACKERS] signal handling in plpython

[Mario De Frutos Dieguez]

Hi!

Thank you very much for your quick response :)

We're looking for a solution at plpython level. My two proposals are a
quick "workaround" that let us interrupt using custom signal handlers in
the python code at plpython level. But I'm looking for something more solid
and your proposal, I've been doing this for 3 days hehe, looks great and I
would LOVE to hear more about it and if you can't guide me a bit more in
order to fully understand it :)

We've been thinking to make something like the PostGIS handler
<https://github.com/postgis/postgis/blob/98b2cdb872b5b5bd65606f5bce334d2477b2afc5/postgis/postgis_module.c#L128>
and
have multiple signal handlers: one for the Postgres using
StatementCancelHandler and one for the python code. How does it sound?

Thank you again for your time :)

2016-10-14 12:01 GMT+02:00 Heikki Linnakangas <hlinn...@iki.fi>:

> On 10/13/2016 08:57 PM, Mario De Frutos Dieguez wrote:
>
>> I come here asking for some advice/help because we're facing some
>> unexpected behavior when we want to interrupt functions doing CPU
>> intensive
>> operations in plpython.
>>
>> Our problem is that we're not able to interrupt them when they're making
>> CPU intensive operations. For example, when calculating Moran using PySAL,
>> the SIGINT handler of Postgres is not able to cancel it.
>>
>
> Python code isn't interruptible, but any queries you run within a python
> function are. So if you have a loop in your function that you know will run
> for a long time, you could issue a dummy "SELECT 1" query every once in a
> while. However, that doesn't help, if the long loop is in a library
> function that you have no control over, rather than the PL/python function
> itself.
>
> It would be nice to have a solution for this in plpython itself, so that
> the query cancel was turned into a Python exception. Patches for that would
> be welcome. I think you could use Py_AddPendingCall() from PostgreSQL's
> signal handler, to schedule a call to a function that in turn throws a
> Python exception. That'll need some changes to PostgreSQL's normal signal
> handlers, like die() and StatementCancelHandler() in postgres.c, but it
> seems doable.
>
> - Heikki
>
>

[HACKERS] signal handling in plpython

[Mario De Frutos Dieguez]

Hello everyone :).

First of all, I want to introduce me to this list. My name is Mario de
Frutos and I work at CARTO :)

I come here asking for some advice/help because we're facing some
unexpected behavior when we want to interrupt functions doing CPU intensive
operations in plpython.

Our problem is that we're not able to interrupt them when they're making
CPU intensive operations. For example, when calculating Moran using PySAL,
the SIGINT handler of Postgres is not able to cancel it.

I want to show you some possible solutions that I've tried without success:

- If we don't add a custom signal handler, we're not able to interrupt the
function when it's making CPU intensive operations. When the `SIGINT`
signal is launched, the system is not able to interrupt it until the
function ends.
- If we add a custom signal handler for the `SIGINT`, we are able to
interrupt the CPU intensive function but we're not able to interrupt data
fetching operations like `plpy.execute(query)` because we have overridden
the Postgres handler for that signal.
- As a third option I've added a python context manager to wrap, for
testing purposes, the CPU intensive part (Moran function from PySAL):
```
def _signal_handler(signal_code, frame):
plpy.error(INTERRUPTED BY USER!!')


@contextmanager
def interruptible():
try:
signal.signal(signal.SIGINT, _signal_handler)
yield
finally:
# Restore the default behavoiur for the signal
signal.signal(signal.SIGINT, signal.SIG_DFL)
```
  This doesn't work as expected because in the `finally` clause we try to
reset to the default behavior but in Postgres, the behavior for the SIGINT
signal is defined by a [custom handler](
https://github.com/postgres/postgres/blob/master/src/include/tcop/tcopprot.h#L66
).
  If we try to retrieve the old handler using `signal.getsignal` we get a
None object

So after all,going back and forth I came up with two possible solutions:
- [custom code
<https://github.com/CartoDB/postgres/commit/5b159b1cce6da38c2c67d4058d544ff9bb179480>]
in `plpython` to make us able to reset the default signal handler after
finish the CPU intensive functions. It seems to work but I'm still doing
some tests. This option lets us call it explicitly and add it to the
`finally` part of a decorator/context manager
- Reset the signal handler at the beginning of the `plpy.execute` or alike
functions like [here
<https://github.com/CartoDB/postgres/commit/5b159b1cce6da38c2c67d4058d544ff9bb179480#diff-4d0cb76412a1c4ee5d9c7f76ee489507R185>
].

As an extra ball, we want to implement the SIGALRM part to mimic the
"statement timeout" behavior too

I don't know if there is a better way to implement this, I know we're
pushing/doing things beyond the scope of plpython but any advise is welcome
:)

Re: [HACKERS] Minor patch for the uuid-ossp extension

[Mario Weilguni]

Am 22.11.2013 16:15, schrieb Tom Lane:
[ memo to self: never, ever accept another contrib module whose name 
isn't a plain SQL identifier ]


Well, in that case and since this is a rarely used extension (I guess 
so), maybe it would be the best to simply rename that extension to 
uuidossp (or whatever) and don't make any special treatment for it?




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

Re: [HACKERS] DELETE with LIMIT (or my first hack)

[Mario Weilguni]

Am 01.12.2010 15:37, schrieb Rob Wultsch:

For beginners, a useful startup option is --safe-updates (or
--i-am-a-dummy, which has the same effect). This option was introduced
in MySQL 3.23.11. It is helpful for cases when you might have issued a
DELETE FROM tbl_name statement but forgotten the WHERE clause.
Normally, such a statement deletes all rows from the table. With
--safe-updates, you can delete rows only by specifying the key values
that identify them. This helps prevent accidents.


Is it really up to the database to decide what queries are ok? It's the 
task of the developers to test their applikations.


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

Re: [HACKERS] Fast REVERSE() function?

[Mario Weilguni]

 (Aside: presumably we could walk thru the string destructively,
 in-place, swapping bytes; I think that would be theoretically
 quickest...)

Hmmm... I guess it will not work für UTF-8 or any other multibyte charset 

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

[HACKERS] pg_compresslog/pg_decompresslog

[Mario Weilguni]

I found the discussion about log compressing here:
http://archives.postgresql.org/pgsql-patches/2007-03/msg00502.php

But I cannot find the scripts (pg_compresslog/pg_decompresslog), how can I get 
those? Will this work for 8.1 branch too? I want to use PITR, but archiving 
over one days will generate too much wal data, so this kind of compression 
would be quite helpful.

Regards,
Mario

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

[HACKERS] Crash in pgCrypto?

[Mario Weilguni]

Could someone using the pgcrypto extension please verify this?

SELECT encode(digest(null, 'md5'::text), 'hex');
or
SELECT digest(null, 'md5');

Takes a few seconds, and then crashes the server with a Signal 11. My 
system is PostgreSQL 8.2.7. Seems to be an unchecked access to memory 
location 0.


Best regards,
Mario Weilguni



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

Re: [HACKERS] Crash in pgCrypto?

[Mario Weilguni]

Marko Kreen schrieb:

On 6/16/08, Mario Weilguni [EMAIL PROTECTED] wrote:
  

Could someone using the pgcrypto extension please verify this?

 SELECT encode(digest(null, 'md5'::text), 'hex');
 or
 SELECT digest(null, 'md5');

 Takes a few seconds, and then crashes the server with a Signal 11. My
system is PostgreSQL 8.2.7. Seems to be an unchecked access to memory
location 0.



Seems you loaded pgcrypto function signatures from dump (from 8.0).

 http://marc.info/?l=postgresql-generalm=118794006505296w=2

Recreate the functions with pgcrypto.sql.

  
Yes, this is what I did, and it happened during update from 8.0 -- 8.2. 
But that brings me to another problem, what is the best way to create 
backups with pgdump so that stuff from contrib is not dumped, but 
recreated from the newer version?


Thanks!




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

Re: [HACKERS] Crash in pgCrypto?

[Mario Weilguni]

Marko Kreen schrieb:

On 6/16/08, Mario Weilguni [EMAIL PROTECTED] wrote:
  

Marko Kreen schrieb:


On 6/16/08, Mario Weilguni [EMAIL PROTECTED] wrote:
  

Could someone using the pgcrypto extension please verify this?

 SELECT encode(digest(null, 'md5'::text), 'hex');
 or
 SELECT digest(null, 'md5');

 Takes a few seconds, and then crashes the server with a Signal 11. My
system is PostgreSQL 8.2.7. Seems to be an unchecked access to memory
location 0.


Seems you loaded pgcrypto function signatures from dump (from 8.0).


  

http://marc.info/?l=postgresql-generalm=118794006505296w=2


Recreate the functions with pgcrypto.sql.

  

 Yes, this is what I did, and it happened during update from 8.0 -- 8.2.
But that brings me to another problem, what is the best way to create
backups with pgdump so that stuff from contrib is not dumped, but recreated
from the newer version?



Good question...  Seems the proper support for modules will not
leave todo-list any time soon.

Only way that works now is to add any module .sql to template0, so
they would not be dumped out.  So you are forced to recreate them
properly on newer version.  (By adding them to template0 again.)

  

Is this todo-list something I can find online?

Best regards
Mario Weilguni


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

Re: [HACKERS] Crash in pgCrypto?

[Mario Weilguni]

Andrew Dunstan schrieb:



Marko Kreen wrote:


Good question...  Seems the proper support for modules will not
leave todo-list any time soon.

Only way that works now is to add any module .sql to template0, so
they would not be dumped out.  So you are forced to recreate them
properly on newer version.  (By adding them to template0 again.)

  


Module install/uninstall is being worked on. In fact this case shows 
that it's something we really need, rather than just something that 
would be nice to have, IMNSHO.


cheers

andrew


Yes, this is really a pitfall when doing security related updates.

Best regards,
Mario Weilguni



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

Re: [HACKERS] a problem when poring from Oracle's PL/SQL to PLPGSQL

[Mario Weilguni]

billy schrieb:
 pgsql-hackers:

 The following is Oracle's PL/SQL

   if resTypeTableName is null
   then
 queryStr := 'select IntIID, Path FROM aaResourceData' || ' where 
 ResType=''' || srcType || ''' and ResID=''' || srcID || ; 
   
   else
   queryStr := 'select 
 IntIID, Path FROM ' || resTypeTableName || ' where ResType=''' || srcType || 
 ''' and ResID=''' ||
 srcID || ;   
   end if;

  open cursorSrc for queryStr;


 Here queryStr is a variable which type is TEXT OR VARCHAR or other string 
 types.

 But in  PLPGSQL, we can only open a cursor this way:

  open cursorSrc for select * from testtable;

 We cannot substitude select * from testtable with a variable.

 Is there another way to handle it?

 Thank you for your help. :-)
   
open cursorSrc for execute queryStr; should work fine

Regards
Mario Weilguni


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

Re: [HACKERS] intercepting WAL writes

[Mario Weilguni]

Mike schrieb:


Hello,

I’m new to the core PostgreSQL code, so pardon the question if the 
answer is really obvious, and I’m just missing it, but I’ve got a 
relatively large web application that uses PostgreSQL as a back-end 
database, and we’re heavily using memcached to cache frequently 
accessed data.


I’m looking at modifying PostgreSQL (in some way) to push changes 
directly to our memcache servers, in hopes of moving towards a system 
where only writes are actually sent to the databases, and reads are 
exclusively sent to the memcache servers.


I’m guessing that I could intercept the WAL writes, and use this 
information to push out to my memcache servers, similar to a 
replication model.


Can somebody point to the most logical place in the code to intercept 
the WAL writes? (just a rough direction would be enough)- or if this 
doesn’t make sense at all, another suggestion on where to get the 
data? (I’m trying to avoid doing it using triggers).


Thanks,

Mike

Why not use rules? They are far more easy to use than patching at C 
Level, and you can simply write some functions at C level and load those 
as a postgres extension, and interact with MemCache at this level.


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

Re: [HACKERS] Setting a pre-existing index as a primary key

[Mario Weilguni]

Tom Lane schrieb:

Jonah H. Harris [EMAIL PROTECTED] writes:
  

I've run into a couple cases now where it would be helpful to easily
assign an already-existing unique index as a primary key.



You need to present a more convincing use-case than this unsupported
assertion.  There's hardly any effective difference between a unique
index + NOT NULL constraints and a declared primary key ... so what
did you really need it for?

  
In fact it seems to be necessary when connecting with ODBC, I had the 
problem a month ago, MsSQL will not work correctly with connected tables 
in a postgres database  when there is no PK. NOT NULL and unique index 
is not enough.


But I think it's overkill to add ALTER commands for this rare corner 
case, maybe it's enough to set indisprimary on the index?



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

[HACKERS] pg_restore oddity?

[Mario Weilguni]

There's a IMO a problem with pg_restore, it should be easy to fix (I 
hope - and I could try to fix it and send a patch).

* I've a dump taken from a 8.1 database
* I'm using gist and ltree
* I'm restoring to a 8.2 database

Problem:
I cannot use -1 for performance, because some gist stuff has changed 
and the restore fails. But there seems to be no option for pg_restore to 
use transactions for data restore, so it's very very slow (one million 
records, each obviously in it's own transaction - because a separate 
session select count(1) from logins shows a growing number).


It would be nice to use transactions for the data stuff itself, but not 
for schema changes or functions. I know I can use separate pg_restore 
runs for schema and data, but it's complicated IMHO.


I see several options:
* Use transactions for data, maybe with a separate command line option
* Use transactions everytime, and place savepoints to recover from errors?

Any ideas what I could do?

Regards
Mario


---(end of broadcast)---
TIP 7: You can help support the PostgreSQL project by donating at

   http://www.postgresql.org/about/donate

Re: [HACKERS] pg_restore oddity?

[Mario Weilguni]

Heikki Linnakangas schrieb:

Mario Weilguni wrote:
  

I cannot use -1 for performance, because some gist stuff has changed
and the restore fails. But there seems to be no option for pg_restore to
use transactions for data restore, so it's very very slow (one million
records, each obviously in it's own transaction - because a separate
session select count(1) from logins shows a growing number).



By default, pg_dump/pg_restore uses a COPY command for each table, and
each COPY executes as a single transaction, so you shouldn't see the row
count growing like that. Is the dump file in --inserts format?

  


You are right, it was my fault. I was confused about the pg_dump syntax, 
and used -d (the -d because pg_restore needs it for the destination 
database, not the dump itself), so it was using --inserts.


Everything is working fine. I've done dump/restores cycles a hundreds 
times, and now such a mistake. I can't believe it.

Seems like I need to take some vacations.

Thanks for the help!



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

Re: [DOCS] [HACKERS] Contrib modules documentation online

[Mario Gonzalez]

On 29/08/2007, Neil Conway [EMAIL PROTECTED] wrote:

 I wonder if it would be possible to keep the master version of the
 contrib docs as SGML, and generate plaintext READMEs from it during the
 documentation build.


  Hello Neil, I think I'm doing something similar but not with README
files. Currently I'm writing the FAQ into Docbook XML, that's why we
can build the HTML and plain text at one.

 I'm going to finish this week then I'll show the results.



-- 
http://www.advogato.org/person/mgonzalez/

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

Re: [HACKERS] Bug in UTF8-Validation Code?

[Mario Weilguni]

Am Sonntag, 18. März 2007 12:36 schrieb Martijn van Oosterhout:

 It seems to me that the easiest solution would be to forbid \x?? escape
 sequences where it's greater than \x7F for UTF-8 server encodings.
 Instead introduce a \u escape for specifying the unicode character
 directly. Under the basic principle that any escape sequence still has
 to represent a single character. The result can be multiple bytes, but
 you don't have to check for consistancy anymore.

Would'nt the best solution to change the order of 
validation/convert_backslash_sequences better? First convert the sequences, 
and reject them in the validation stage?

Regards
Mario Weilguni

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

   http://archives.postgresql.org

Re: [HACKERS] Bug in UTF8-Validation Code?

[Mario Weilguni]

Am Mittwoch, 14. März 2007 08:01 schrieb Michael Paesold:
 Andrew Dunstan wrote:
 
  This strikes me as essential. If the db has a certain encoding ISTM we
  are promising that all the text data is valid for that encoding.
 
  The question in my mind is how we help people to recover from the fact
  that we haven't done that.

 I would also say that it's a bug that escape sequences can get characters
 into the database that are not valid in the specified encoding. If you
 compare the encoding to table constraints, there is no way to simply
 escape a constraint check.

 This seems to violate the principle of consistency in ACID. Additionally,
 if you include pg_dump into ACID, it also violates durability, since it
 cannot restore what it wrote itself.
 Is there anything in the SQL spec that asks for such a behaviour? I guess
 not.

 A DBA will usually not even learn about this issue until they are presented
 with a failing restore.

Is there anything I can do to help with this problem? Maybe implementing a new 
GUC variable that turns off accepting wrong encoded sequences (so DBAs still 
can turn it on if they really depend on it)?

For me, 

Best regards,
Mario Weilguni

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

[HACKERS] Bug in UTF8-Validation Code?

[Mario Weilguni]

Hi,

I've a problem with a database, I can dump the database to a file, but 
restoration fails, happens with 8.1.4.

Steps to reproduce:
create database testdb with encoding='UTF8';
\c testdb
create table test(x text);
insert into test values ('\244'); == Is akzepted, even if not UTF8.

pg_dump testdb -f testdb.dump -Fc
pg_restore -f testdb.dump -d testdb = fails with an error: 
ERROR:  invalid byte sequence for encoding UTF8: 0xa4

The problem itself comes from a CSV file, which is imported with \copy without 
proper quoting (so I have to fix this anyway), but I still think this is an 
error, making restoration very complicated in such cases...

Or am I doing something completly wrong here?

Best regards,
Mario Weilguni


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

Re: [HACKERS] Bug in UTF8-Validation Code?

[Mario Weilguni]

Am Dienstag, 13. März 2007 14:46 schrieb Albe Laurenz:
 Mario Weilguni wrote:
  Steps to reproduce:
  create database testdb with encoding='UTF8';
  \c testdb
  create table test(x text);
  insert into test values ('\244'); == Is akzepted, even if not UTF8.

 This is working as expected, see the remark in
 http://www.postgresql.org/docs/current/static/sql-syntax-lexical.html#SQ
 L-SYNTAX-STRINGS

 It is your responsibility that the byte sequences you create
  are valid characters in the server character set encoding.

In that case, pg_dump is doing wrong here and should quote the output. IMO it 
cannot be defined as working as expected, when this makes any database dumps 
worthless, without any warnings at dump-time.

pg_dump should output \244 itself in that case.

Best regards,
Mario Weilguni

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

Re: [HACKERS] Bug in UTF8-Validation Code?

[Mario Weilguni]

Am Dienstag, 13. März 2007 15:12 schrieb Andrew Dunstan:
 The sentence quoted from the docs is perhaps less than a model of
 clarity. I would take it to mean that no client-encoding -
 server-encoding translation will take place. Does it really mean that
 the server will happily accept any escaped byte sequence, whether or not
 it is valid for the server encoding? If so that seems ... odd.

Yes, \octal sequences are accepted even if invalid. The problem is, pgdump 
will happily dump those sequences as is, so in that case a char ascii 0xa4 is 
emitted, and so the dump cannot be restored with pg_restore. 

A dangerous feature IMO, and will make a lot of DB admins very unhappy if they 
have to validate every day if the precious database dumps can be restored in 
case of an error. 

Best regards,
Mario Weilguni



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

Re: [HACKERS] Bug in UTF8-Validation Code?

[Mario Weilguni]

Am Dienstag, 13. März 2007 16:38 schrieb Joshua D. Drake:
 Andrew Dunstan wrote:
  Albe Laurenz wrote:
  A fix could be either that the server checks escape sequences for
  validity
 
  This strikes me as essential. If the db has a certain encoding ISTM we
  are promising that all the text data is valid for that encoding.
 
  The question in my mind is how we help people to recover from the fact
  that we haven't done that.

 Is this any different than the issues of moving 8.0.x to 8.1 UTF8? Where
 we had to use iconv?


What issues? I've upgraded several 8.0 database to 8.1. without having to use 
iconv. Did I miss something?

Regards,
Mario Weilguni

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

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

[HACKERS] ERROR: failed to build any 8-way joins

[Mario Weilguni]

Interesting problem, seems to be planer related:
select 1
  from beitraege bei,
   b_zuordnungen bz,
   (select bei_id 
 from b_zuordnungen bz, 
  ben_zuordnungen z, 
  strukturelemente se 
where se.id = z.str_id 
  and se.sty_id = (select id from strukturtypen where code='GEO') 
  and z.str_id = bz.str_id 
  and z.ben_id = 100
union 
select id from beitraege where kz_edit  'N' and useraend = 100
   ) as foo
  where bz.bei_id = bei.id  
and foo.bei_id = bei.id
and bei.red_id in (select gba.grp_id
 from grp_ben_applikationen gba,
  grp_gruppen grp
where grp.id = gba.grp_id
  and grp.kz_aktiv='J'
  and gba.app_id in (select id from grp_applikationen where 
code in ('app1', 'app2')) 
  and gba.ben_id = 100)
and (bei.bei_id_frei is null or bei.kz_edit='N')
and (bei.bt_id, bz.str_id) in ((96,1259036), (96,2688382) )
and bei.red_id=112
and bei.id in (
  select bzu.bei_id
from b_zuordnungen bzu,
 strukturelemente se
   where bzu.str_id = se.id
 and se.id = 1773715
)
 and bei.id=10157309;
ERROR:  failed to build any 8-way joins

Interesting: remove any of the above where conditions solves  the problem go 
away, e.g. removing and bei.id=10157309.

Testet with 8.2.1 and 8.1.4, same effect on both systems. Because of the large 
number of tables involved it's difficult to find a self contained patch, but if 
necessary I'll give it a try.
I could give 8.2.3 a try, but I doubt this will help. 

Any ideas?

Regards
Mario Weilguni

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

   http://archives.postgresql.org

Re: [HACKERS] ERROR: failed to build any 8-way joins

[Mario Weilguni]

Thanks for the info. Is there a fix for 8.1 branch, the production system is 
still 8.1.

Regards
Mario Weilguni


Am Donnerstag, 15. Februar 2007 16:25 schrieb Alvaro Herrera:
 Mario Weilguni wrote:
  Interesting: remove any of the above where conditions solves  the problem
  go away, e.g. removing and bei.id=10157309.
 
  Testet with 8.2.1 and 8.1.4, same effect on both systems. Because of the
  large number of tables involved it's difficult to find a self contained
  patch, but if necessary I'll give it a try. I could give 8.2.3 a try, but
  I doubt this will help.

 I think a similar problem was fixed after 8.2.3 was released, so you may
 want to check out the REL8_2_STABLE branch.

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

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

[HACKERS] to_char not IMMUTABLE?

[Mario Weilguni]

I had a problem when upgrading a database from 8.1.4 to 8.2.1:
Sorry, the error messages are in german.

pg_restore: [archiver (db)] Error while PROCESSING TOC:
pg_restore: [archiver (db)] Error from TOC entry 1531; 1259 3477393 INDEX 
idx_inspektionen_dat_inspektion 
pg_restore: [archiver (db)] could not execute query: FEHLER:  Funktionen im 
Indexausdruck muessen als IMMUTABLE markiert sein
Command was: CREATE INDEX idx_inspektionen_dat_inspektion ON inspektionen 
USING btree (to_char(dat_inspektion, ''::text));
WARNING: errors ignored on restore: 1

to_char(timestamp, '') should be constant and marked immutable, or am I 
wrong here? Or is it not marked immutable because of possible changes on 
date_format?

Regards,
Mario Weilguni

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

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

Re: [HACKERS] to_char not IMMUTABLE?

[Mario Weilguni]

Am Freitag, 12. Januar 2007 14:48 schrieb Martijn van Oosterhout:
 On Fri, Jan 12, 2007 at 11:55:07AM +0100, Mario Weilguni wrote:
  to_char(timestamp, '') should be constant and marked immutable,
  or am I wrong here? Or is it not marked immutable because of possible
  changes on date_format?

 AIUI, to_char is not immutable because it can be effected by external
 variables, like LC_TIME.

 As it is though, I'm not sure why you're using to_char here, surely
 extract or date_truc would be more appropriate?

Thanks for the info. Changing this to use extract is no real problem, I was 
just curious if this is intendend behaviour.

Best regards,
Mario Weilguni

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

Re: [HACKERS] InitPostgres and flatfiles question

[Mario Weilguni]

Am Donnerstag, 4. Januar 2007 16:36 schrieb Tom Lane:
 Markus Schiltknecht [EMAIL PROTECTED] writes:
 Hm, that's an interesting point.  psql's -c just shoves its whole
 argument string at the backend in one PQexec(), instead of dividing
 at semicolons as psql does with normal input.  And so it winds up as
 a single transaction because postgres.c doesn't force a transaction
 commit until the end of the querystring.  But that's not a transaction
 block in the normal sense and so it doesn't trigger the
 PreventTransactionChain defense in CREATE DATABASE and elsewhere.

 I wonder whether we ought to change that?  The point of
 PreventTransactionChain is that we don't want the user rolling back
 the statement post-completion, but it seems that
   psql -c 'CREATE DATABASE foo; ABORT; BEGIN; ...'
 would bypass the check.

Maybe not directly related to that problem, but I had a problem with -c last 
month, when I noticed that this will not work:

psql -c set client_encoding=iso-8859-1; select name from customer (UTF8 
database, output is hmmm... broken german umlauts).

Best regards
Mario Weilguni

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

Re: [HACKERS] psql: core dumped

[Mario]

On 19/12/06, Alvaro Herrera [EMAIL PROTECTED] wrote:


I think the problem Mario is really trying to solve is quitting at
psql's Password:  prompt.  Ctrl-C is ignored at that point apparently.
SIGQUIT (thus Ctrl-\ in most people's setup) does it but it also dumps
core.



 yes, that is true and also when you are is psql prompt too. But, I
had no idea that a core dump is normal with a SIGQUIT signal.

 Thank you all for your time and patience; this was my first try
with a postgres patch, I'll try with something else in other chance
:-)





--
http://www.advogato.org/person/mgonzalez/

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

Re: [HACKERS] psql: core dumped

[Mario]

On 19/12/06, Andrew Dunstan [EMAIL PROTECTED] wrote:


This normally a SIGQUIT, and on my machine at least the default action for
that is a core dump. Perhaps you need to say what you are trying to do and
why.



 I'd like to help :-)   I wanted to avoid a core dumped but you told
me that's a normal thing for a SIGQUIT signal.





--
http://www.advogato.org/person/mgonzalez/

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

[HACKERS] psql: core dumped

[Mario]

When psql is running and CRTL + \ is pressed, a core dumped show up.
In first place I ran psql into gdb, saw the backtrace and I believed
it was a libc6 bug and I reported to my distro security team
https://launchpad.net/distros/ubuntu/+source/glibc/+bug/76437

 Ubuntu edgy has got libc-2.4, a friend of my tested in Linux with
libc-2.3 and nothing happened. I don't know why in my libc version
(2.4) this happen.

 So, with humility :-) I want to help and I wrote a patch with a
solution, you can have it in the next link:
http://media.forestal.udec.cl/pgsql/startup.diff  I do not wanted to
sent it as an attachment

 My solution is catch the SIGQUIT signal and abort in silence, just that.


  Regards!

PS: sorry my English
--
http://www.advogato.org/person/mgonzalez/

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

Re: [HACKERS] psql: core dumped

[Mario]

On 20/12/06, Peter Eisentraut [EMAIL PROTECTED] wrote:

Mario wrote:
  When psql is running and CRTL + \ is pressed, a core dumped show up.
 In first place I ran psql into gdb, saw the backtrace and I believed
 it was a libc6 bug and I reported to my distro security team
 https://launchpad.net/distros/ubuntu/+source/glibc/+bug/76437

This isn't a bug.  It's working as designed.



  Even if you get a core dumped every time you press CTRL+\  ?  why?





--
http://www.advogato.org/person/mgonzalez/

---(end of broadcast)---
TIP 7: You can help support the PostgreSQL project by donating at

   http://www.postgresql.org/about/donate

Re: [HACKERS] ALTER TABLE RENAME column

[Mario Weilguni]

 Uh, we did that years ago.

Really? 
+ o Add ALTER TABLE RENAME COLUMN (should rename appropriate sequences and 
constraints)

Sounds like this is not done, at least not renaming sequencens and constraints, 
or am I wrong here?

Regard
Mario Weilguni

-Ursprüngliche Nachricht-
Von: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Im Auftrag von Tom Lane
Gesendet: Freitag, 17. November 2006 07:05
An: Jim Nasby
Cc: PostgreSQL Hackers
Betreff: Re: [HACKERS] ALTER TABLE RENAME column 

Jim Nasby [EMAIL PROTECTED] writes:
 Any reason not to support renaming columns? Can this be added to TODO?

Uh, we did that years ago.

regards, tom lane

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

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

Re: [HACKERS] ALTER TABLE RENAME column

[Mario Weilguni]

Right, but I think jim means automatical renames of sequences, and especially 
something like this:
db=# CREATE TABLE foo (bar serial);
NOTICE:  CREATE TABLE will create implicit sequence foo_bar_seq for serial 
column foo.bar
CREATE TABLE
db=# ALTER TABLE foo rename bar to baf;
ALTER TABLE
db=# \d foo
  Table public.foo
 Column |  Type   | Modifiers
+-+---
 baf| integer | not null default nextval('foo_bar_seq'::regclass)

The sequence still is named foo_bar_seq.

IMO this should do:
Alter sequence foo_bar_seq rename to foo_baf_seq;
Alter table foo alter baf set default nextval('foo_baf_seq')



-Ursprüngliche Nachricht-
Von: Jonah H. Harris [mailto:[EMAIL PROTECTED] 
Gesendet: Freitag, 17. November 2006 16:27
An: Mario Weilguni
Cc: Tom Lane; Jim Nasby; PostgreSQL Hackers
Betreff: Re: [HACKERS] ALTER TABLE RENAME column

On 11/17/06, Mario Weilguni [EMAIL PROTECTED] wrote:
 Sounds like this is not done, at least not renaming sequencens and 
 constraints, or am I wrong here?

To rename a sequence or a table:

ALTER TABLE yo_table RENAME TO yo_new_table; ALTER TABLE yo_sequence RENAME TO 
yo_new_sequence;

Or am I mistaken?

--
Jonah H. Harris, Software Architect | phone: 732.331.1300
EnterpriseDB Corporation| fax: 732.331.1301
33 Wood Ave S, 2nd Floor| [EMAIL PROTECTED]
Iselin, New Jersey 08830| http://www.enterprisedb.com/

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

   http://archives.postgresql.org

[HACKERS] subscribe

[Mario Guenterberg]

Subscribe

-- 
Wenn Sie glauben, dies sei ein großes Problem - warten Sie mal ab,
bis wir versuchen die Lösung zu finden. (Walter Matthau)

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

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

Re: [HACKERS] bug or feature, || -operator and NULLs

[Mario Weilguni]

Am Mittwoch, 18. Oktober 2006 13:52 schrieb Andreas Joseph Krogh:
 This has been been discussed before, but Oracle behaves differently, and
 IMHO in a more correct way.

 The following query returns NULL in PG:
 SELECT NULL || 'fisk';

 But in Oracle, it returns 'fisk':
 SELECT NULL || 'fisk' FROM DUAL;

 The latter seems more logical...

I've worked alot with oracle a few years ago and I agree, the feature is handy 
and makes sometimes life easier, but it's simply wrong. I heard a while ago 
that newer oracle versions changed this to sql - standard, is this true?


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

Re: [HACKERS] bug or feature, || -operator and NULLs

[Mario Weilguni]

If you want this behaviour you will have to explicitly handle it with
COALESCE().

regards,
Lukas

True. But there's a point where oracle is really better here, they named
coalesce nvl = a lot easier to type ;-)

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

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

Re: [HACKERS] bug or feature, || -operator and NULLs

[Mario Weilguni]

Nice, but I still prefer nvl. Coalesce is hard to pronounce, and even harder to 
type.

-Ursprüngliche Nachricht-
Von: Andreas Joseph Krogh [mailto:[EMAIL PROTECTED] 
Gesendet: Mittwoch, 18. Oktober 2006 15:48
An: pgsql-hackers@postgresql.org
Cc: Mario Weilguni
Betreff: Re: [HACKERS] bug or feature, || -operator and NULLs

On Wednesday 18 October 2006 15:15, Mario Weilguni wrote:
 If you want this behaviour you will have to explicitly handle it with

 COALESCE().

 regards,
 Lukas

 True. But there's a point where oracle is really better here, they 
 named coalesce nvl = a lot easier to type ;-)

They actually support COALESCE now and explicit JOINs too.

--
Andreas Joseph Krogh [EMAIL PROTECTED] Senior Software Developer / Manager 
gpg public_key: http://dev.officenet.no/~andreak/public_key.asc
+-+
OfficeNet AS| The most difficult thing in the world is to |
Karenslyst Allé 11  | know how to do a thing and to watch |
PO. Box 529 Skøyen  | somebody else doing it wrong, without   |
0214 Oslo   | comment.|
NORWAY  | |
Mobile: +47 909  56 963 | |
+-+

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

Re: [HACKERS] bug or feature, || -operator and NULLs

[Mario Weilguni]

Yes it's hard for me, maybe because I am no native english speaker. 

-Ursprüngliche Nachricht-
Von: Andrew Dunstan [mailto:[EMAIL PROTECTED] 
Gesendet: Mittwoch, 18. Oktober 2006 16:11
An: Lukas Kahwe Smith
Cc: Mario Weilguni; pgsql-hackers@postgresql.org
Betreff: Re: [HACKERS] bug or feature, || -operator and NULLs

Lukas Kahwe Smith wrote:
 Mario Weilguni wrote:
 Nice, but I still prefer nvl. Coalesce is hard to pronounce, and even 
 harder to type.

 amen .. coalesce was invented by a sadistic twit (something which 
 people have also called me .. so it goes).

Perhaps people are trying to pronounce it wrongly. According to m-w, the right 
ways is:

   Pronunciation: kO--'les


or more informally koh a less.

Is that really so hard?

cheers

andrew



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

Re: [HACKERS] lower() not working correctly...?

[Mario Weilguni]

This works as intended. Try this:
select coalesce(lower(firstname), '') || ' ' || coalesce(lower(lastname), '') 
from person

Concating something unknown (=NULL) and a string = unknown (=NULL)



-Ursprüngliche Nachricht-
Von: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Im Auftrag von Andreas Joseph 
Krogh
Gesendet: Freitag, 15. September 2006 09:53
An: pgsql-hackers@postgresql.org
Betreff: [HACKERS] lower() not working correctly...?

I have the following query:

select lower(firstname) || ' ' || lower(lastname) from person

firstname and lastname are VARCHAR

lower() returns NULL when firstname OR lastname is NULL, is this correct?


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

   http://archives.postgresql.org

Re: [HACKERS] Fixed length data types issue

[Mario Weilguni]

What about the char type? Isn't it designed for that? Or will this type 
disappear in future releases?
 

-Ursprüngliche Nachricht-
Von: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Im Auftrag von Heikki 
Linnakangas
Gesendet: Freitag, 15. September 2006 13:35
An: Martijn van Oosterhout
Cc: [EMAIL PROTECTED]; pgsql-hackers@postgresql.org
Betreff: Re: [HACKERS] Fixed length data types issue

Martijn van Oosterhout wrote:
 I don't think making a special typlen value just for a type that can 
 store a single UTF-8 character is smart. I just can't see enough use 
 to make it worth it.
   

Assuming that we can set encoding per-column one day, I agree. If you have a 
CHAR(1) field, you're going to store codes like 'A', 'B', 'C' or '1', '2', '3' 
in it, and you don't need UTF-8 for that.

--
  Heikki Linnakangas
  EnterpriseDB   http://www.enterprisedb.com


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

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

[HACKERS] I need your help!!

[mario tovilla]

Hi!! I hope you are not too busy.
The thing is that i need to connect my pc which is on windows XP and the 
server is on linux with postgres.


The question is?
how do I connect my windows machine to the server with postgres?
What do I have to do?, put it in steps please!!!

thanks you guys are great!!!

_
Prodigy/MSN Search. Todo lo que buscas ahora más rapido  
http://search.t1msn.com.mx/



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

[HACKERS] Another Ltree/GiST problem

[Mario Weilguni]

I've a table that stores hierarchical information in a ltree field for fast 
access, indexed with a GiST index.

Interesting Problem with ltree/GiST index: sometimes, the index will get 
corrupt, as seen by the examples below:

WRONG
db=# SELECT path from strukturelemente where path ~ 
'142.2330445.2330526.5358672.5358675.5358752.*';
 path
--
(0 rows)

RIGHT
db=# SELECT path from strukturelemente where path ~ 
'*.2330445.2330526.5358672.5358675.5358752.*';
path
-
 142.2330445.2330526.5358672.5358675.5358752
(1 row)

db=# SELECT path from strukturelemente where path ~ '*.5358752.*';  
  path
-
 142.2330445.2330526.5358672.5358675.5358752
(1 row)


db=# EXPLAIN ANALYZE SELECT path from strukturelemente where path ~ 
'*.5358752.*';
 QUERY PLAN 

-
 Bitmap Heap Scan on strukturelemente  (cost=2.05..27.99 rows=14 width=45) 
(actual time=4.617..4.618 rows=1 loops=1)
   Recheck Cond: (path ~ '*.5358752.*'::lquery)
   -  Bitmap Index Scan on str_uk4  (cost=0.00..2.05 rows=14 width=0) (actual 
time=4.604..4.604 rows=1 loops=1)
 Index Cond: (path ~ '*.5358752.*'::lquery)
 Total runtime: 4.690 ms
(5 rows)


db=# EXPLAIN ANALYZE SELECT path from strukturelemente where path ~ 
'142.2330445.2330526.5358672.5358675.5358752.*';
 QUERY PLAN 

-
 Bitmap Heap Scan on strukturelemente  (cost=2.05..27.99 rows=14 width=45) 
(actual time=0.030..0.030 rows=0 loops=1)
   Recheck Cond: (path ~ 
'142.2330445.2330526.5358672.5358675.5358752.*'::lquery)
   -  Bitmap Index Scan on str_uk4  (cost=0.00..2.05 rows=14 width=0) (actual 
time=0.027..0.027 rows=0 loops=1)
 Index Cond: (path ~ 
'142.2330445.2330526.5358672.5358675.5358752.*'::lquery)
 Total runtime: 0.081 ms
(5 rows)


db=# SELECT path from strukturelemente where path ~ 
'142.2330445.2330526.5358672.5358675.*.5358752.*';
 path
--
(0 rows)

When doing sequential scans (set enable_indexscan to off and set 
enable_bitmapscan to off) everything works as expected.


db=# SELECT path from strukturelemente where path ~ 
'142.2330445.2330526.5358672.*.5358675.5358752.*';
path
-
 142.2330445.2330526.5358672.5358675.5358752
(1 row)


After an update (and turning index access on), everything will work fine again:
UPDATE strukturelemente set id=id where id=5358752;

I get this:
db=# SELECT path from strukturelemente where path ~ 
'142.2330445.2330526.5358672.5358675.5358752.*';
path
-
 142.2330445.2330526.5358672.5358675.5358752
(1 row)

db=# SELECT path from strukturelemente where path ~ 
'142.2330445.2330526.5358672.5358675.*';  
path
-
 142.2330445.2330526.5358672.5358675.5358752
(1 row)



Reindexing the index also fixes the problem, but regular reindexing cannot be a 
solution, since there is still a timeframe where wrong results are returned.
I've made a copy of the index-file when it was broken, after the update and 
after reindexing. Is there a tool for getting a human-readable dump of the 
index?

Using PostgreSQL 8.1.4 with ltree version from CVS (since there's another bug 
fixed in CVS). Problem occurs on a 2 node cluster of 2 Quad-CPU system (Xeon, 2 
physical CPU's, 2 hyperthreading), one node running the database, the other one 
the webserver.

Any ideas?

Best regards,
Mario Weilguni


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

[HACKERS] RESET CONNECTION?

[Mario Weilguni]

Will this patch make it into 8.2?
http://archives.postgresql.org/pgsql-patches/2004-12/msg00228.php

It's a really nice feature, would be extremly useful with tools like pgpool.

Am Freitag, 7. Juli 2006 19:13 schrieb Bruce Momjian:
 There are roughly three weeks left until the feature freeze on August 1.
 If people are working on items, they should be announced before August
 1, and the patches submitted by August 1.  If the patch is large, it
 should be discussed now and an intermediate patch posted to the lists
 soon.

 FYI, we don't have many major features ready for 8.2.

 --
   Bruce Momjian   [EMAIL PROTECTED]
   EnterpriseDBhttp://www.enterprisedb.com

   + If your life is a hard drive, Christ can be your backup. +

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

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

Re: [HACKERS] vacuum, performance, and MVCC

[Mario Weilguni]

Am Donnerstag, 22. Juni 2006 16:09 schrieb Csaba Nagy:
   [...]
   There has to be a more linear way of handling this scenario.
 
  So vacuum the table often.

 Good advice, except if the table is huge :-)

 Here we have for example some tables which are frequently updated but
 contain 100 million rows. Vacuuming that takes hours. And the dead row
 candidates are the ones which are updated again and again and looked up
 frequently...

 A good solution would be a new type of vacuum which does not need to do
 a full table scan but can clean the pending dead rows without that... I
 guess then I could vacuum really frequently those tables.

Now that there is autovaccum, why not think of something like continous 
vacuum? A background process that gets info about potential changed tuples, 
and vacuums them (only those tuples), possibly with honouring I/O needs of 
backgrounds (not steealing I/O from busy backends).

For sure not that easy as autovacuum. I'm pretty sure I've read something 
about partial vacuum lately, is somebody working on something like this?

Regards,
  Mario

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

Re: [HACKERS] BEGIN inside transaction should be an error

[Mario Weilguni]

Am Donnerstag, 11. Mai 2006 22:16 schrieb Simon Riggs:
 On Wed, 2006-05-10 at 21:24 -0400, Tom Lane wrote:
  Martijn van Oosterhout kleptog@svana.org writes:
   How do other database deal with this? Either they nest BEGIN/COMMIT or
   they probably throw an error without aborting the transaction, which is
   pretty much what we do. Is there a database that actually aborts a
   whole transaction just for an extraneous begin?
 
  Probably not.  The SQL99 spec does say (in describing START TRANSACTION,
  which is the standard spelling of BEGIN)
 
   1) If a start transaction statement statement is executed when
  an SQL-transaction is currently active, then an exception condition is
  raised: invalid transaction state - active SQL-transaction.
 
  *However*, they are almost certainly expecting that that condition only
  causes the START command to be ignored; not that it should bounce the
  whole transaction.  So I think the argument that this is required by
  the spec is a bit off base.

 If you interpret the standard that way then the correct behaviour in the
 face of *any* exception condition should be *not* abort the transaction.
 In PostgreSQL, all exception conditions do abort the transaction, so why
 not this one? Why would we special-case this?

IMO it's ok to raise an exception - if this is configurable for at least one 
releasy cycle - giving developers time to fix applications. It's no good 
behaviour to change something like this without any (at least time-limited ) 
backward compatible option.

regards
mario weilguni

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

   http://archives.postgresql.org

Re: [HACKERS] BEGIN inside transaction should be an error

[Mario Weilguni]

Am Mittwoch, 10. Mai 2006 08:19 schrieb Tom Lane:
 Dennis Bjorklund [EMAIL PROTECTED] writes:
  Yesterday I helped a guy on irc with a locking problem, he thought
  that locking in postgresql was broken. It turned out that he had a PHP
  function that he called inside his transaction and the function did BEGIN
  and COMMIT. Since BEGIN inside a transaction is just a warning what
  happend was that the inner COMMIT ended the transaction and
  released the locks. The rest of his commands ran with autocommit
  and no locks and he got broken data into the database.
 
  Could we make BEGIN fail when we already are in a transaction?

 We could, but it'd probably break about as many apps as it fixed.
 I wonder whether php shouldn't be complaining about this, instead
 --- doesn't php have its own ideas about controlling where the
 transaction commit points are?

In fact it would break many application, so it should be at least controllable 
by a setting or GUC.

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

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

Re: [HACKERS] BEGIN inside transaction should be an error

[Mario Weilguni]

Am Mittwoch, 10. Mai 2006 09:41 schrieb Mario Weilguni:
 Am Mittwoch, 10. Mai 2006 08:19 schrieb Tom Lane:
  Dennis Bjorklund [EMAIL PROTECTED] writes:
   Yesterday I helped a guy on irc with a locking problem, he thought
   that locking in postgresql was broken. It turned out that he had a PHP
   function that he called inside his transaction and the function did
   BEGIN and COMMIT. Since BEGIN inside a transaction is just a warning
   what happend was that the inner COMMIT ended the transaction and
   released the locks. The rest of his commands ran with autocommit
   and no locks and he got broken data into the database.
  
   Could we make BEGIN fail when we already are in a transaction?
 
  We could, but it'd probably break about as many apps as it fixed.
  I wonder whether php shouldn't be complaining about this, instead
  --- doesn't php have its own ideas about controlling where the
  transaction commit points are?

 In fact it would break many application, so it should be at least
 controllable by a setting or GUC.


No, I want that there is a setting or GUC that controls whether an error or a 
warning is raised when begin is executed within a transaction. I know of 
several php database wrappers that will be seriously broken when errors are 
raised...

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

   http://archives.postgresql.org

Re: [HACKERS] BEGIN inside transaction should be an error

[Mario Weilguni]

Am Mittwoch, 10. Mai 2006 10:59 schrieb Peter Eisentraut:
 Am Mittwoch, 10. Mai 2006 10:10 schrieb Martijn van Oosterhout:
  You want to make a GUC that makes:
 
  BEGIN;
  BEGIN;
 
  Leave you with an aborted transaction? That seems like a singularly
  useless feature...

 If a command doesn't do what it is supposed to do, then it should be an
 error. That seems like a throroughly useful feature to me.

Maybe. I just want to emphasize that it will break existing applications.


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

   http://archives.postgresql.org

Re: [HACKERS] BEGIN inside transaction should be an error

[Mario Weilguni]

Am Mittwoch, 10. Mai 2006 11:44 schrieb Bernd Helmle:
 --On Mittwoch, Mai 10, 2006 10:14:22 +0200 Mario Weilguni

 [EMAIL PROTECTED] wrote:
  No, I want that there is a setting or GUC that controls whether an error
  or a  warning is raised when begin is executed within a transaction. I
  know of  several php database wrappers that will be seriously broken when
  errors are  raised...

 Such a behavior is already broken by design. I think it's not desirable to
 blindly do
 transaction start or commit without tracking the current transaction state.
 So these wrappers
 need to be fixed first.

You mean broken like transform_null_equals? Or add_missing_from? 

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

Re: [HACKERS] Strange results from to_timestamp

[Mario Weilguni]

to_timestamp is only for Oracle compatibility? I always thought it's some sort 
of sql standard. What's the sql compliant way to do this?

Regards,
mario weilguni


-Ursprüngliche Nachricht-
Von: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Im Auftrag von Tom Lane
Gesendet: Freitag, 07. April 2006 06:09
An: Mario Weilguni
Cc: PostgreSQL-development
Betreff: Re: [HACKERS] Strange results from to_timestamp 

Mario Weilguni [EMAIL PROTECTED] writes:
 I think all except the first one should raise a warning, isn't it?

to_timestamp (and friends) all seem to me to act pretty bizarre when faced with 
input that doesn't match the given format string.  However, in the end that is 
an Oracle-compatibility function, and there is only one measure of what it 
should do: what does Oracle do in the same case.
Can anyone try these examples on a recent Oracle version?

regards, tom lane

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

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

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

[HACKERS] Strange results from to_timestamp

[Mario Weilguni]

mydb=# select to_timestamp(' 0300','mmdd hh24mi');
   to_timestamp
---
 0001-01-01 03:00:00+01 BC
(1 row)

Questionable, but probably valid.



mydb=# select to_timestamp(' 0300','mmdd hh24mi');
  to_timestamp

 0300-12-25 03:00:00+01
(1 row)

This puzzles me. Where is the 25th of december coming from?



mydb=# select to_timestamp(' 030004','mmdd hh24mi');
  to_timestamp

 0382-04-23 03:00:00+01
(1 row)

Same as above.


mydb=# select to_timestamp(' 040004','mmdd hh24mi');
  to_timestamp

 0509-10-10 04:00:00+01


I think all except the first one should raise a warning, isn't it? Where can I 
find the source code of this function?

Best regards,
Mario Weilguni


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

Re: [HACKERS] Strange results from to_timestamp

[Mario Weilguni]

Am Donnerstag, 6. April 2006 14:57 schrieb Mario Weilguni:
 mydb=# select to_timestamp(' 0300','mmdd hh24mi');
to_timestamp
 ---
  0001-01-01 03:00:00+01 BC
 (1 row)

 Questionable, but probably valid.



 mydb=# select to_timestamp(' 0300','mmdd hh24mi');
   to_timestamp
 
  0300-12-25 03:00:00+01
 (1 row)

 This puzzles me. Where is the 25th of december coming from?

Sorry, forgot to mention, this is from PostgreSQL 8.1.3

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

[HACKERS] Deadlock with ShareLocks?

[Mario Weilguni]

I've an interesting error with deadlocks on a quite simple table:

Version: 8.1
Query: update last_modified set dataend=now() where type='list'
DB-Error : ERROR:  deadlock detected
DETAIL:  Process 10454 waits for ShareLock on transaction 1347632; blocked by 
process 15920.
Process 15920 waits for ShareLock on transaction 1347633; blocked by process 
10454.

I thought ShareLock is not really blocking, or am I wrong? The bad thing is, 
that it's quite difficult to get the info what the other backend was doing at 
the same time. Maybe it would be a good idea to log the statements of both 
backends (if available). Would make detecting such errors quite easier.

Best Regards,
Mario Weilguni

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

Re: [HACKERS] Deadlock with ShareLocks?

[Mario Weilguni]

Am Dienstag, 13. Dezember 2005 16:35 schrieb Tom Lane:
 Mario Weilguni [EMAIL PROTECTED] writes:
  Version: 8.1
  Query: update last_modified set dataend=now() where type='list'
  DB-Error : ERROR:  deadlock detected
  DETAIL:  Process 10454 waits for ShareLock on transaction 1347632;
  blocked by process 15920.
  Process 15920 waits for ShareLock on transaction 1347633; blocked by
  process 10454.
 
  I thought ShareLock is not really blocking, or am I wrong?

 You're wrong.  This looks like a deadlock occasioned by trying to update
 the same two rows in different orders in different transactions.  In a
 pre-8.1 release I'd have guessed that this might be a deadlock on
 foreign key master rows, but in 8.1 that can't happen anymore.

 If WHERE type = 'list' selects multiple rows, and someone else might
 be trying to update more than one of those same rows using a different
 WHERE clause, deadlock is definitely possible.  You may not have much
 choice but to take a table-level lock before starting the updates.

Hi Tom,

there must be something different here. In fact, this is the real data from 
the table:
 type  |dataend
---+---
 applikationen | 2004-09-03 14:44:44.63422+02
 xslt  | 2005-12-07 21:30:08.183392+01
 red   | 2005-12-08 19:36:50.357642+01
 list  | 2005-12-13 14:35:44.544795+01
 struktur  | 2005-12-13 16:21:52.645182+01

  Table public.last_modified
 Column  |   Type   | Modifiers
-+--+---
 type| character varying(32)| not null
 dataend | timestamp with time zone | not null
Indexes:
last_modified_pkey PRIMARY KEY, btree (type)

Since the type field is PK, there cannot be 2 rows with type='list', I guess 
the deadlock must have some different explanation. There are no foreign key 
constraints, triggers, rules involved.

Best regards,
Mario Weilguni

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

Re: [HACKERS] Deadlock with ShareLocks?

[Mario Weilguni]

Am Dienstag, 13. Dezember 2005 16:52 schrieb Tom Lane:
 Mario Weilguni [EMAIL PROTECTED] writes:
  Since the type field is PK, there cannot be 2 rows with type='list', I
  guess the deadlock must have some different explanation.

 Then the deadlock must involve rows in two different tables.  What else
 are you doing in the same transaction(s) as updating last_modified?

That's what I think too, unfortunatly, I can't say for sure. The application 
uses around 1000 different queries, and I had no logging on for this case. So 
I guess two backends might have issued interleaved updates.

I think without logging this cannot be solved here. 

Best regards,
Mario Weilguni

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

Re: [HACKERS] Replication on the backend

[Mario Weilguni]

IMO this is not true. You can get affordable 10GBit network adapters, so you 
can have plenty of bandwith in a db server pool (if they are located in the 
same area). Even 1GBit Ethernet greatly helps here, and would make it possible 
to balance read-intensive (and not write intensive) applications. We using 
linux bonding interface with 2 gbit NICs, and 200 MBytes/sec throughput is 
something you need to have a quite some harddisks to reach that. Latency is not 
bad too.

Regards,
Mario weilguni


-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Chris Browne
Sent: Tuesday, December 06, 2005 4:43 PM
To: pgsql-hackers@postgresql.org
Subject: Re: [HACKERS] Replication on the backend

[EMAIL PROTECTED] (Gustavo Tonini) writes:
 But,  wouldn't the performance be better? And wouldn't asynchronous
 messages be better processed?

Why do you think performance would be materially affected by this?

The MAJOR performance bottleneck is normally the slow network
connection between servers.

When looked at in the perspective of that bottleneck, pretty much
everything else is just noise.  (Sometimes pretty loud noise, but
still noise :-).)
-- 
let name=cbbrowne and tld=cbbrowne.com in name ^ @ ^ tld;;
http://cbbrowne.com/info/spreadsheets.html
When the grammar checker identifies an error, it suggests a
correction and can even makes some changes for you.  
-- Microsoft Word for Windows 2.0 User's Guide, p.35:

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

   http://archives.postgresql.org

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

Re: [HACKERS] Strange left join problems in 8.1

[Mario Weilguni]

Title: AW: [HACKERS] Strange left join problems in 8.1 






I've tried this with cvs , -r REL8_1_STABLE (hope this was the right one), and works flawless now.

Thanks a lot!

Best regards
Mario Weilguni


-Ursprüngliche Nachricht-
Von: Tom Lane [mailto:[EMAIL PROTECTED]]
Gesendet: Sa 03.12.2005 17:53
An: Mario Weilguni
Cc: pgsql-hackers@postgresql.org
Betreff: Re: [HACKERS] Strange left join problems in 8.1

Mario Weilguni [EMAIL PROTECTED] writes:
 It seems like the planner is pulling the last where condition into the
 second left join, evaluating it in wrong order.

Can you try this on 8.1 branch tip? It sounds suspiciously similar to
an already-fixed issue (see reports from Sebastian Böck around
mid-November). You haven't provided a complete test case so I really
can't investigate for myself ...

   regards, tom lane

[HACKERS] Strange left join problems in 8.1

[Mario Weilguni]

 is eliminated:
select 
count(1) from beitraege 
bei left join (select 
* 
from b_ltk_protokoll 
blp, 
abw_mailqueue mq 
 
where mq.id = 
blp.mq_id 
and blp.grund = 
'notify_verschickt_frei' 
) as foo on ( foo.bei_id = bei.id ) where bei.id = 10078101and 
(foo.kz_verschicken is null or foo.kz_verschicken = 'N');
count--- 1(1 
row)
--Aggregate 
(cost=10.76..10.77 rows=1 width=0) (actual time=0.239..0.241 rows=1 
loops=1) - Nested Loop Left Join 
(cost=0.00..10.76 rows=1 width=0) (actual time=0.221..0.226 rows=1 
loops=1) Join Filter: 
("inner".bei_id = 
"outer".id) Filter: 
(("inner".kz_verschicken IS NULL) OR (("inner".kz_verschicken)::text = 
'N'::text)) - 
Index Scan using beitraege_pkey on beitraege bei (cost=0.00..3.91 rows=1 
width=4) (actual time=0.036..0.038 rows=1 
loops=1) 
Index Cond: (id = 10078101) 
- Nested Loop (cost=0.00..6.83 rows=1 width=9) (actual 
time=0.175..0.175 rows=0 
loops=1) 
Join Filter: (("inner".kz_verschicken IS NULL) OR 
(("inner".kz_verschicken)::text = 
'N'::text)) 
- Index Scan using b_ltk_protokoll_bei_id_key on b_ltk_protokoll 
blp (cost=0.00..3.65 rows=1 width=8) (actual time=0.125..0.129 rows=1 
loops=1) 
Index Cond: ((bei_id = 10078101) AND (grund = 
'notify_verschickt_frei'::text)) 
- Index Scan using abw_mailqueue_pkey on abw_mailqueue mq 
(cost=0.00..3.17 rows=1 width=9) (actual time=0.017..0.020 rows=1 
loops=1) 
Index Cond: (mq.id = "outer".mq_id)Total runtime: 0.528 ms(13 
rows)(same queryplan as above)

Here's the query 
plan for 8.0, slightly other data, but correct behavior
select 
count(1)
from beitraege 
bei left join (b_ltk_protokoll 
blp 
left join abw_mailqueue mq on (mq.id = blp.mq_id)

 
) on ( blp.bei_id = 
bei.id 
and blp.grund = 'notify_verschickt_frei' 
)where bei.id = 10194579 and 
(mq.kz_verschicken is null or mq.kz_verschicken = 'N');
count--- 0(1 
row)
Nested Loop Left 
Join (cost=89.68..93.85 rows=1 width=21) (actual time=1.574..1.574 rows=0 
loops=1) Join Filter: ("inner".bei_id = 
"outer".id) Filter: (("inner".kz_verschicken IS NULL) OR 
(("inner".kz_verschicken)::text = 'N'::text)) - Index 
Scan using beitraege_pkey on beitraege bei (cost=0.00..3.68 rows=1 
width=4) (actual time=0.029..0.031 rows=1 
loops=1) Index Cond: (id = 
10194579) - Merge Left Join (cost=89.68..90.03 
rows=9 width=21) (actual time=1.476..1.528 rows=3 
loops=1) Merge Cond: 
("outer".mq_id = "inner".id) 
- Sort (cost=68.31..68.33 rows=9 width=8) (actual 
time=1.205..1.207 rows=3 
loops=1) 
Sort Key: 
blp.mq_id 
- Seq Scan on b_ltk_protokoll blp (cost=0.00..68.16 rows=9 
width=8) (actual time=0.115..1.189 rows=3 
loops=1) 
Filter: (grund = 
''notify_verschickt_frei'::text) 
- Sort (cost=21.37..21.52 rows=60 width=17) (actual 
time=0.230..0.266 rows=60 
loops=1) 
Sort Key: 
mq.id 
- Seq Scan on abw_mailqueue mq (cost=0.00..19.60 rows=60 
width=17) (actual time=0.006..0.166 rows=60 loops=1)Total runtime: 
1.702 ms(15 rows)


It seems like the 
planner is pulling the last where condition into the second left join, 
evaluating it in wrong order.

Any idea what's 
going wrong here?

Best 
regards,
 
Mario Weilguni

Re: [HACKERS] Strange left join problems in 8.1

[Mario Weilguni]

Can you try this on 8.1 branch tip? It sounds suspiciously similar to


an already-fixed issue (see reports from Sebastian Böck around
mid-November).  You haven't provided a complete test case so I really
can't investigate for myself ...

regards, tom lane
 

Of course I can give it a try, do I need to re-initdb when I use cvs to 
checkout 8.1 tip? I don't think so, isn't it?


Best regards,
   Mario Weilguni


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

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

Re: Hashjoin startup strategy (was Re: [HACKERS] Getting different number of results when using hashjoin on/off)

[Mario Weilguni]

Hello Tom,

I tried both patches on a different machine (but had to take the patches from 
cvs diff, cut'n paste from the mail-program did not work). Up until now, they 
work like a charm, correct results and fast. I will try on the other machine 
that failed yesterday in the afternoon, maybe it was just a problem with 
patching (or with the machine setup itself, it's a hardened gentoo, but I doubt 
that).

BTW: the difference of the second patch is really noticable, from 990ms down to 
226ms.

Thanks for your quick response! I wish commercial vendors would act that fast 
:-)

Best regards,
Mario Weilguni



icomedias - Digitale Kommunikation

Mario Weilguni, Forschung und Entwicklung
[EMAIL PROTECTED], http://www.icomedias.com/

icomedias Österreich Systemhaus GmbH:
  8020 Graz, Entenplatz 1 
  Tel: +43 (316) 721.671-272, Fax: -103  

icomedias Deutschland Systemhaus GmbH:
  10969 Berlin, Alexandrinenstraße 2-3
  Tel: +49 (30) 695.399-272, Fax: -103

-Original Message-
From: Tom Lane [mailto:[EMAIL PROTECTED] 
Sent: Tuesday, November 29, 2005 12:50 AM
To: Mario Weilguni; pgsql-hackers@postgresql.org
Subject: Re: Hashjoin startup strategy (was Re: [HACKERS] Getting different 
number of results when using hashjoin on/off) 

I wrote:
 For a query like this, where the hash join is being done repeatedly,
 it might be useful for the executor itself to track how often each
 subplan has been seen to be empty.

I implemented a simple form of this, and it made 8.1 faster than 8.0
on the test case I was using.  Give it a try ...

regards, tom lane


Index: src/backend/executor/nodeHashjoin.c
===
RCS file: /cvsroot/pgsql/src/backend/executor/nodeHashjoin.c,v
retrieving revision 1.75.2.2
diff -c -r1.75.2.2 nodeHashjoin.c
*** src/backend/executor/nodeHashjoin.c 28 Nov 2005 17:14:47 -  1.75.2.2
--- src/backend/executor/nodeHashjoin.c 28 Nov 2005 23:41:28 -
***
*** 120,135 
 * since we aren't going to be able to skip the join on the 
strength
 * of an empty inner relation anyway.)
 *
 * The only way to make the check is to try to fetch a tuple 
from the
 * outer plan node.  If we succeed, we have to stash it away 
for later
 * consumption by ExecHashJoinOuterGetTuple.
 */
!   if (outerNode-plan-startup_cost  
hashNode-ps.plan-total_cost ||
!   node-js.jointype == JOIN_LEFT)
{
node-hj_FirstOuterTupleSlot = ExecProcNode(outerNode);
if (TupIsNull(node-hj_FirstOuterTupleSlot))
return NULL;
}
else
node-hj_FirstOuterTupleSlot = NULL;
--- 120,147 
 * since we aren't going to be able to skip the join on the 
strength
 * of an empty inner relation anyway.)
 *
+* If we are rescanning the join, we make use of information 
gained
+* on the previous scan: don't bother to try the prefetch if the
+* previous scan found the outer relation nonempty.  This is not
+* 100% reliable since with new parameters the outer relation 
might
+* yield different results, but it's a good heuristic.
+*
 * The only way to make the check is to try to fetch a tuple 
from the
 * outer plan node.  If we succeed, we have to stash it away 
for later
 * consumption by ExecHashJoinOuterGetTuple.
 */
!   if (node-js.jointype == JOIN_LEFT ||
!   (outerNode-plan-startup_cost  
hashNode-ps.plan-total_cost 
!!node-hj_OuterNotEmpty))
{
node-hj_FirstOuterTupleSlot = ExecProcNode(outerNode);
if (TupIsNull(node-hj_FirstOuterTupleSlot))
+   {
+   node-hj_OuterNotEmpty = false;
return NULL;
+   }
+   else
+   node-hj_OuterNotEmpty = true;
}
else
node-hj_FirstOuterTupleSlot = NULL;
***
*** 159,164 
--- 171,183 
 * scanning the outer relation
 */
hashtable-nbatch_outstart = hashtable-nbatch;
+ 
+   /*
+* Reset OuterNotEmpty for scan.  (It's OK if we fetched a tuple
+* above, because ExecHashJoinOuterGetTuple will immediately
+* set it again.)
+*/
+   node-hj_OuterNotEmpty = false

Re: Hashjoin startup strategy (was Re: [HACKERS] Getting different number of results when using hashjoin on/off)

[Mario Weilguni]

Am Dienstag, 29. November 2005 10:05 schrieb Mario Weilguni:
 Hello Tom,

 I tried both patches on a different machine (but had to take the patches
 from cvs diff, cut'n paste from the mail-program did not work). Up until
 now, they work like a charm, correct results and fast. I will try on the
 other machine that failed yesterday in the afternoon, maybe it was just a
 problem with patching (or with the machine setup itself, it's a hardened
 gentoo, but I doubt that).

I've taken diff's from cvs for both files, and now it works flawlessly on both 
machines, with good response times. 

I guess I must have done something wrong when copying the patch from your 
yesterday's mail.

Thanks!

Best regards
Mario

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

[HACKERS] Getting different number of results when using hashjoin on/off

[Mario Weilguni]

I've a problem that might be a bug in the core system (hashjoins) or with ltree 
using gist-index, but I fail miserable to produce a useful testcase (using 8.1, 
worked in 8.0):

A query produces wrong (=0) results, when a different plan is enforced, I get a 
merge-join plan that looks similar, but produces the correct result (=16 rows).

I can post a queryplan, but cannot post the data itself since it's confidental 
(though I might be able to randomize some data and construct a self contained 
case, but this would take quite some time).


The working case is:
set enable_hashjoin to off;
 Seq Scan on foo1 cost=0.00..423583.57 rows=10810 width=4) (actual 
time=675.422..706.815 rows=16 loops=1)
   Filter: (subplan)
   SubPlan
 -  Merge Join  (cost=19.49..19.55 rows=1 width=0) (actual 
time=0.028..0.028 rows=0 loops=21619)
   Merge Cond: (outer.str_id = inner.id)
   -  Sort  (cost=6.49..6.50 rows=5 width=4) (actual time=0.023..0.023 
rows=0 loops=21619)
 Sort Key: bz.str_id
 -  Bitmap Heap Scan on foo2 bz  (cost=2.02..6.43 rows=5 
width=4) (actual time=0.012..0.012 rows=0 loops=21619)
   Recheck Cond: (bid = $0)
   -  Bitmap Index Scan on foo2_bid_key1  (cost=0.00..2.02 
rows=5 width=0) (actual time=0.009..0.009 rows=0 loops=21619)
 Index Cond: (bid = $0)
   -  Sort  (cost=13.00..13.01 rows=6 width=4) (actual 
time=0.002..0.003 rows=1 loops=136)
 Sort Key: str.id
 -  Bitmap Heap Scan on structure str  (cost=2.02..12.92 
rows=6 width=4) (actual time=0.095..0.097 rows=1 loops=1)
   Recheck Cond: (path ~ 
'142.2330445.2330598.2330676.*'::lquery)
   -  Bitmap Index Scan on str_uk4  (cost=0.00..2.02 
rows=6 width=0) (actual time=0.086..0.086 rows=1 loops=1)
 Index Cond: (path ~ 
'142.2330445.2330598.2330676.*'::lquery)
 Total runtime: 707.019 ms

16 rows...


The failing case is:
set enable_hashjoin to on;
 Seq Scan on foo1 cost=0.00..421679.00 rows=10810 width=4) (actual 
time=154.663..154.663 rows=0 loops=1)
   Filter: (subplan)
   SubPlan
 -  Hash Join  (cost=8.47..19.46 rows=1 width=0) (actual time=0.004..0.004 
rows=0 loops=21619)
   Hash Cond: (outer.id = inner.str_id)
   -  Bitmap Heap Scan on structure str  (cost=2.02..12.92 rows=6 
width=4) (actual time=0.100..30.095 rows=1 loops=1)
 Recheck Cond: (path ~ '142.2330445.2330598.2330676.*'::lquery)
 -  Bitmap Index Scan on str_uk4  (cost=0.00..2.02 rows=6 
width=0) (actual time=0.090..0.090 rows=1 loops=1)
   Index Cond: (path ~ 
'142.2330445.2330598.2330676.*'::lquery)
   -  Hash  (cost=6.43..6.43 rows=5 width=4) (actual time=0.032..0.032 
rows=0 loops=1)
 -  Bitmap Heap Scan on foo2 bz  (cost=2.02..6.43 rows=5 
width=4) (actual time=0.025..0.025 rows=0 loops=1)
   Recheck Cond: (bid = $0)
   -  Bitmap Index Scan on foo2_bid_key1  (cost=0.00..2.02 
rows=5 width=0) (actual time=0.021..0.021 rows=0 loops=1)
 Index Cond: (bid = $0)
 Total runtime: 154.862 ms
No rows

The query itself is quite simple:
select foo1.id
from foo1
where 
  foo1.datloesch is null
  and exists (select 1 
from foo2 bz,
 structure str
   where bz.bid=foo1.id
 and str.id = bz.str_id
 and str.path ~ '*.2330676.*'
  );

The path field is an ltree column, with an GIST index on it.


Any ideas what I could try to track this down?

Best regards,
Mario Weilguni

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

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

Re: [HACKERS] Getting different number of results when using hashjoin on/off

[Mario Weilguni]

Am Montag, 28. November 2005 14:12 schrieb Christopher Kings-Lynne:
  The path field is an ltree column, with an GIST index on it.

 Something to do with bitmap indexscans on lossy indexes?

 Chris

I doubt that, set enable_bitmapscan to off produces the wrong result as 
well.

Best regards
Mario 

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

   http://archives.postgresql.org

Re: [HACKERS] Getting different number of results when using hashjoin on/off

[Mario Weilguni]

Thanks Tom for you quick answer!

No, I'm using 8.1.0, and tried it on different machines, always the same 
results.

SELECT version();
PostgreSQL 8.1.0 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.3.4 20040623 
(Gentoo Hardened Linux 3.3.4-r1, ssp-3.3.2-2, pie-8.7.6)

Best regards,
Mario Weilguni 


icomedias - Digitale Kommunikation

Mario Weilguni, Forschung und Entwicklung
[EMAIL PROTECTED], http://www.icomedias.com/

icomedias Österreich Systemhaus GmbH:
  8020 Graz, Entenplatz 1 
  Tel: +43 (316) 721.671-272, Fax: -103  

icomedias Deutschland Systemhaus GmbH:
  10969 Berlin, Alexandrinenstraße 2-3
  Tel: +49 (30) 695.399-272, Fax: -103

-Original Message-
From: Tom Lane [mailto:[EMAIL PROTECTED] 
Sent: Monday, November 28, 2005 5:20 PM
To: Mario Weilguni
Cc: Mario Weilguni; pgsql-hackers@postgresql.org
Subject: Re: [HACKERS] Getting different number of results when using hashjoin 
on/off 

Mario Weilguni [EMAIL PROTECTED] writes:
 Yes. This is from a 8.0.3 (with slightly older and different data,
 resulting in only 9 rows, but the rest is the same):

Yeah, that looks more reasonable.

I tried to reproduce this, without any luck:

regression=# explain analyze select count(*) from tenk1 a where exists (select 
1 from tenk1 b, tenk1 c where b.unique1=c.unique2 and b.hundred in (4,5) and 
c.hundred=a.hundred);
   QUERY 
PLAN

 Aggregate  (cost=3879742.37..3879742.38 rows=1 width=0) (actual 
time=46579.077..46579.082 rows=1 loops=1)
   -  Seq Scan on tenk1 a  (cost=0.00..3879729.87 rows=5000 width=0) (actual 
time=5.129..46528.208 rows=8500 loops=1)
 Filter: (subplan)
 SubPlan
   -  Hash Join  (cost=229.20..546.66 rows=2 width=0) (actual 
time=4.569..4.569 rows=1 loops=1)
 Hash Cond: (outer.unique1 = inner.unique2)
 -  Bitmap Heap Scan on tenk1 b  (cost=4.69..321.15 rows=196 
width=4) (actual time=0.947..1.698 rows=90 loops=1)
   Recheck Cond: ((hundred = 4) OR (hundred = 5))
   -  BitmapOr  (cost=4.69..4.69 rows=197 width=0) (actual 
time=0.544..0.544 rows=0 loops=1)
 -  Bitmap Index Scan on tenk1_hundred  
(cost=0.00..2.34 rows=98 width=0) (actual time=0.271..0.271 rows=100 
loops=1)
   Index Cond: (hundred = 4)
 -  Bitmap Index Scan on tenk1_hundred  
(cost=0.00..2.34 rows=98 width=0) (actual time=0.262..0.262 rows=100 
loops=1)
   Index Cond: (hundred = 5)
 -  Hash  (cost=224.26..224.26 rows=100 width=4) (actual 
time=2.370..2.370 rows=100 loops=1)
   -  Bitmap Heap Scan on tenk1 c  (cost=2.35..224.26 
rows=100 width=4) (actual time=0.492..1.616 rows=100 loops=1)
 Recheck Cond: (hundred = $0)
 -  Bitmap Index Scan on tenk1_hundred  
(cost=0.00..2.35 rows=100 width=0) (actual time=0.278..0.278 rows=100 
loops=1)
   Index Cond: (hundred = $0)
 Total runtime: 46584.654 ms
(19 rows)

(I'm not bothering with setting up an ltree index, since the question
of what index is being used shouldn't affect hashjoin's decision to
rescan or not.)

That's using 8.1 branch CVS tip, but there aren't any related bug fixes
since 8.1 release.  We did have several bug fixes in the hash join code
during the 8.1 beta cycle though ... is it possible you are really
running an 8.1 beta and not 8.1.0?

regards, tom lane

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

   http://archives.postgresql.org

Re: [HACKERS] Getting different number of results when using hashjoin on/off

[Mario Weilguni]

Hello Tom,

Thanks for the quick response, I've tried the patch, but it did not work
as expected. When I set enable_hashjoin to off, everything works as
expected, but with hashjoin on I do not even get results anymore, CPU is
going up to 100% and after 3 minutes I cancelled the query (it normale
would take ~100-500 milliseconds).

I will check the patch on a different machine again and inform you of
the results.

Best regards,
Mario Weilguni


-Original Message-
From: Tom Lane [mailto:[EMAIL PROTECTED] 
Sent: Monday, November 28, 2005 6:09 PM
To: Mario Weilguni
Cc: pgsql-hackers@postgresql.org
Subject: Re: [HACKERS] Getting different number of results when using
hashjoin on/off 

Mario Weilguni [EMAIL PROTECTED] writes:
 No, I'm using 8.1.0, and tried it on different machines, always the
same results.

I see it, I think: the recent changes to avoid work when one or the
other side of the hash join is empty would exit the hash join leaving
a state that confused ExecReScanHashJoin() into thinking it didn't
have to do anything.  Try the attached patch.

regards, tom lane


Index: src/backend/executor/nodeHashjoin.c
===
RCS file: /cvsroot/pgsql/src/backend/executor/nodeHashjoin.c,v
retrieving revision 1.75.2.1
diff -c -r1.75.2.1 nodeHashjoin.c
*** src/backend/executor/nodeHashjoin.c 22 Nov 2005 18:23:09 -
1.75.2.1
--- src/backend/executor/nodeHashjoin.c 28 Nov 2005 17:04:43 -
***
*** 152,163 
 * outer join, we can quit without scanning the outer
relation.
 */
if (hashtable-totalTuples == 0  node-js.jointype !=
JOIN_LEFT)
-   {
-   ExecHashTableDestroy(hashtable);
-   node-hj_HashTable = NULL;
-   node-hj_FirstOuterTupleSlot = NULL;
return NULL;
-   }
  
/*
 * need to remember whether nbatch has increased since
we began
--- 152,158 
***
*** 487,493 
{
ExecHashTableDestroy(node-hj_HashTable);
node-hj_HashTable = NULL;
-   node-hj_FirstOuterTupleSlot = NULL;
}
  
/*
--- 482,487 
***
*** 805,841 
  ExecReScanHashJoin(HashJoinState *node, ExprContext *exprCtxt)
  {
/*
-* If we haven't yet built the hash table then we can just
return; nothing
-* done yet, so nothing to undo.
-*/
-   if (node-hj_HashTable == NULL)
-   return;
- 
-   /*
 * In a multi-batch join, we currently have to do rescans the
hard way,
 * primarily because batch temp files may have already been
released. But
 * if it's a single-batch join, and there is no parameter change
for the
 * inner subnode, then we can just re-use the existing hash
table without
 * rebuilding it.
 */
!   if (node-hj_HashTable-nbatch == 1 
!   ((PlanState *) node)-righttree-chgParam == NULL)
!   {
!   /* okay to reuse the hash table; needn't rescan inner,
either */
!   }
!   else
{
!   /* must destroy and rebuild hash table */
!   ExecHashTableDestroy(node-hj_HashTable);
!   node-hj_HashTable = NULL;
!   node-hj_FirstOuterTupleSlot = NULL;
  
!   /*
!* if chgParam of subnode is not null then plan will be
re-scanned by
!* first ExecProcNode.
!*/
!   if (((PlanState *) node)-righttree-chgParam == NULL)
!   ExecReScan(((PlanState *) node)-righttree,
exprCtxt);
}
  
/* Always reset intra-tuple state */
--- 799,830 
  ExecReScanHashJoin(HashJoinState *node, ExprContext *exprCtxt)
  {
/*
 * In a multi-batch join, we currently have to do rescans the
hard way,
 * primarily because batch temp files may have already been
released. But
 * if it's a single-batch join, and there is no parameter change
for the
 * inner subnode, then we can just re-use the existing hash
table without
 * rebuilding it.
 */
!   if (node-hj_HashTable != NULL)
{
!   if (node-hj_HashTable-nbatch == 1 
!   ((PlanState *) node)-righttree-chgParam ==
NULL)
!   {
!   /* okay to reuse the hash table; needn't rescan
inner, either */
!   }
!   else
!   {
!   /* must destroy and rebuild hash table */
!   ExecHashTableDestroy(node-hj_HashTable);
!   node-hj_HashTable = NULL;
  
!   /*
!* if chgParam of subnode is not null then plan
will be re-scanned
!* by first ExecProcNode

Re: [HACKERS] Getting different number of results when using hashjoin on/off

[Mario Weilguni]

Yes. This is from a 8.0.3 (with slightly older and different data,
resulting in only 9 rows, but the rest is the same):

 Index Scan using ben_uk3 on foo1 ben  (cost=0.00..73867.23 rows=863
width=27) (actual time=38.591..501.839 rows=9 loops=1)
   Filter: (subplan)
   SubPlan
 -  Hash Join  (cost=14.25..42.53 rows=1 width=0) (actual
time=0.284..0.284 rows=0 loops=1725)
   Hash Cond: (outer.id = inner.str_id)
   -  Index Scan using str_uk4 on structure str
(cost=0.00..27.91 rows=13 width=4) (actual time=0.765..4.043 rows=1
loops=112)
 Index Cond: (path ~ '*.2330676.*'::lquery)
   -  Hash  (cost=14.23..14.23 rows=10 width=4) (actual
time=0.012..0.012 rows=0 loops=1725)
 -  Index Scan using foo2_ben_id_key1 on foo2 bz
(cost=0.00..14.23 rows=10 width=4) (actual time=0.008..0.009 rows=1
loops=1725)
   Index Cond: (ben_id = $0)
 Total runtime: 501.980 ms

Best regards

P.s. sorry for the stupid quoting, I've to use Outlook


Mario Weilguni [EMAIL PROTECTED] writes:
 The failing case is:
 ...
SubPlan
  -  Hash Join  (cost=8.47..19.46 rows=1 width=0) (actual
time=0.004..0.004 rows=0 loops=21619)
Hash Cond: (outer.id = inner.str_id)
-  Bitmap Heap Scan on structure str  (cost=2.02..12.92
rows=6 width=4) (actual time=0.100..30.095 rows=1 loops=1)
  Recheck Cond: (path ~
'142.2330445.2330598.2330676.*'::lquery)
  -  Bitmap Index Scan on str_uk4  (cost=0.00..2.02
rows=6 width=0) (actual time=0.090..0.090 rows=1 loops=1)
Index Cond: (path ~
'142.2330445.2330598.2330676.*'::lquery)
-  Hash  (cost=6.43..6.43 rows=5 width=4) (actual
time=0.032..0.032 rows=0 loops=1)
  -  Bitmap Heap Scan on foo2 bz  (cost=2.02..6.43
rows=5 width=4) (actual time=0.025..0.025 rows=0 loops=1)
Recheck Cond: (bid = $0)
-  Bitmap Index Scan on foo2_bid_key1
(cost=0.00..2.02 rows=5 width=0) (actual time=0.021..0.021 rows=0
loops=1)
  Index Cond: (bid = $0)

Hmm, I wonder why the hash join's input nodes are showing loops=1 ...
the hash depends on the subplan parameter $0 so it needs to be
re-evaluated each time through.  It looks like that's not happening.
Do you have the corresponding results from 8.0 --- if so, what do
the loop counts look like?


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

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

Re: Hashjoin startup strategy (was Re: [HACKERS] Getting different number of results when using hashjoin on/off)

[Mario Weilguni]

Title: AW: Hashjoin startup strategy (was Re: [HACKERS] Getting different number of results when using hashjoin on/off)






If the query runs slow it will be not such a problem, but I was very concerned about other queries having this problem too - without knowing it. I've already rewritten the query to use IN instead of exists.

I'll compile again and try it again.

Thanks alot!

Best regards,
Mario Weilguni



-Ursprüngliche Nachricht-
Von: Tom Lane [mailto:[EMAIL PROTECTED]]
Gesendet: Mo 28.11.2005 19:39
An: Mario Weilguni
Cc: pgsql-hackers@postgresql.org
Betreff: Hashjoin startup strategy (was Re: [HACKERS] Getting different number of results when using hashjoin on/off)

Mario Weilguni [EMAIL PROTECTED] writes:
 Thanks for the quick response, I've tried the patch, but it did not work
 as expected. When I set enable_hashjoin to off, everything works as
 expected, but with hashjoin on I do not even get results anymore, CPU is
 going up to 100% and after 3 minutes I cancelled the query (it normale
 would take ~100-500 milliseconds).

Try letting it run longer. I think your expectation is tuned for the
broken implementation (which runs the subqueries only once instead of
26k times...)

The test case I developed for this failure in the regression database is

select count(*) from tenk1 a
where exists (select 1 from tenk1 b, tenk1 c
 where b.unique1=c.unique2 and
 b.hundred in (4,5) and c.hundred=a.hundred+99);

8.0 prefers a nestloop for the subquery, and that plan runs in about
600 ms on my machine. If forced to a hash join, it takes about 2450 ms.
8.1 prefers the hash join to start with, but takes 11300 ms to run it :-(
(after the patch that is).

The reason for the differential is that 8.1 guesses wrong about which
subplan to cycle first: most of the time, the inner plan is empty and
so there's no need to pull any rows from the outer plan, but 8.1 pulls
the first row from the outer plan anyway, and doing that 1 times is
what's eating the extra runtime. It looks from your previous message
that similar things are happening with your data distribution, allowing
8.0 to run faster for you than 8.1 does.

Not sure if there's much we can do about this. The presence of the
upper-query parameter in the subplan makes it difficult to derive any
stats at all, let alone guess how often the subplan will be completely
empty, so I'm not sure the planner can help.

For a query like this, where the hash join is being done repeatedly,
it might be useful for the executor itself to track how often each
subplan has been seen to be empty. In particular, the executor knows
that the outer subplan is parameterless and therefore should deliver
the same results each time (modulo volatile functions of course), so
after the first cycle it could know that there's no point in trying
the early fetch on that side. Dunno if this will be of wide enough
use to be worth implementing though --- in simple cases the join
won't be rescanned and so the executor can't help.

Anyone have any other ideas?

   regards, tom lane

Re: [HACKERS] MS SQL Server compatibility functions

[Mario Weilguni]

Am Mittwoch, 23. November 2005 16:32 schrieb Pollard, Mike:
 If this gets added as a contrib, here's a version of uniqueidentifier
 and newid() I wrote that maintains the same format as the SQL Server
 version:

 CREATE SCHEMA sqlserver
   AUTHORIZATION postgres;
 GRANT ALL ON SCHEMA sqlserver TO public;

 CREATE SEQUENCE sqlserver.uniqueidentifier0
   INCREMENT 1
   MINVALUE 0
   MAXVALUE 9223372036854775807
   START 0
   CACHE 1;
 GRANT ALL ON TABLE sqlserver.uniqueidentifier0 TO public;

 CREATE SEQUENCE sqlserver.uniqueidentifier1
   INCREMENT 1
   MINVALUE 0
   MAXVALUE 9223372036854775807
   START 0
   CACHE 1;
 GRANT ALL ON TABLE sqlserver.uniqueidentifier1 TO public;

Why do you use GRANT ALL and not GRANT SELECT, UPDATE? All means everybody 
can do bad things with those sequences.

Regards,
Mario Weilguni

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

   http://archives.postgresql.org

[HACKERS] Reproducable deadlock situation (possibly with foreign keys)

[Mario Weilguni]

I've a problem occurring daily for me, I get quite a few deadlocks every day, 
and the concurrency is not so high. Happens with postgresql 8.0 and 8.1. as 
well...

Here's a self-contained testcase, which I think it might be the problem I have 
in our production database. While it might be some sort of theoretical 
problem, it happens, the referenced tables are never really updated, but are 
just lookup-tables. In the production systen it's a lot more complicated, 
there are at least 10 different lookup tables, and not all table contain 
references to all lookup-tables:

create table lookup1 (
  id int primary key,
  t text
);
 
create table lookup2 (
  id int primary key,
  t text
);
 
insert into lookup1 values (1, 'test1');
insert into lookup1 values (2, 'test2');
 
insert into lookup2 values (3, 'test3');
insert into lookup2 values (4, 'test4');
 
create table master1 (
id  int primary key,
l1_id int  references lookup1(id),
l2_id int  references lookup2(id),
t text
);
 
create table master2 (
id  int primary key,
l2_id int  references lookup2(id),
l1_id int  references lookup1(id),
t text
);
 
insert into master1 values (1000, 1, 3);
insert into master2 values (1001, 3, 1);
 

T1: BEGIN;
T2: BEGIN;
-- these are the queries similar to those from the foreign key code
T1: SELECT 1 FROM ONLY lookup1 x WHERE id = 1 FOR UPDATE OF x;
T2: SELECT 1 FROM ONLY lookup2 x WHERE id = 3 FOR UPDATE OF x;
T1: SELECT 1 FROM ONLY lookup2 x WHERE id = 3 FOR UPDATE OF x;
T2: SELECT 1 FROM ONLY lookup1 x WHERE id = 1 FOR UPDATE OF x;
-- DEADLOCK OCCURS!
T1: UPDATE master1 set t='foo' where id=1000;
T2: UPDATE master2 set t='foo' where id=1001;
 

IMO it should be possible to solve this IF the foreign key code reorders the 
for update queries in a well-defined order, maybe ordered by the oid of the 
pgclass entry.

In my case, it always happens on INSERT activity (no updates on those tables, 
just inserts), but I hope the above problem might be the solution for the 
insert deadylock too.

Does this sound reasonable?

Regards,
Mario Weilguni

p.s. Is it possible to modify logging so that the SELECT 1 FROM ONLY are 
logged? Maybe this could help me finding out which queries the foreign key 
code really issues.



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

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

Re: [HACKERS] Reproducable deadlock situation (possibly with foreign keys)

[Mario Weilguni]

Ok, this my fault, and you're right.

I took the query from the error messages (a 8.0.3 DB) and applied it to a 8.1 
DB on a testing system without thinking too much.

Still I think reordering those queries might prevent a deadlock.

Best regards

Am Mittwoch, 16. November 2005 12:21 schrieb Alvaro Herrera:
 Mario Weilguni wrote:

 Hi,

  T1: BEGIN;
  T2: BEGIN;
  -- these are the queries similar to those from the foreign key code
  T1: SELECT 1 FROM ONLY lookup1 x WHERE id = 1 FOR UPDATE OF x;
  T2: SELECT 1 FROM ONLY lookup2 x WHERE id = 3 FOR UPDATE OF x;
  T1: SELECT 1 FROM ONLY lookup2 x WHERE id = 3 FOR UPDATE OF x;
  T2: SELECT 1 FROM ONLY lookup1 x WHERE id = 1 FOR UPDATE OF x;
  -- DEADLOCK OCCURS!
  T1: UPDATE master1 set t='foo' where id=1000;
  T2: UPDATE master2 set t='foo' where id=1001;

 Actually, in 8.1 the FK code issues queries like

 T1: SELECT 1 FROM ONLY lookup1 x WHERE id = 1 FOR SHARE OF x;

 which takes only a share lock on the tuple, not an exclusive lock, which
 solves the blocking and deadlocking problem.  If you have a test case
 where it fails on 8.1 I certainly want to see it.

  p.s. Is it possible to modify logging so that the SELECT 1 FROM
  ONLY are logged? Maybe this could help me finding out which queries
  the foreign key code really issues.

 Hmm, actually, those queries should be logged normally, because AFAIK
 they are issued just like any other query, via SPI.

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

[HACKERS] FW:How to modify a tuple returned by SPI_execute

[Mario Weilguni]

Sorry for offtopic, since the pgsql-general ML does not seem to work I post 
here.

I checked the documentation, and still do not get it. I can use SPI_copytuple 
to return a modified version of a tuple, but how do I modify a column of 
type HeapTuple.

In my case, I just want to modify a INT32 column for sorting.

Any ideas?

Regards,
Mario Weilguni

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

Re: VACUUM/t_ctid bug (was Re: [HACKERS] GiST concurrency commited)

[Mario Weilguni]

Am Dienstag, 30. August 2005 11:25 schrieb Teodor Sigaev:
 Fixed in 8.0, 7.4 and 7.3 branches.

 Tom Lane wrote:
  Teodor Sigaev [EMAIL PROTECTED] writes:
 http://www.sigaev.ru/gist/concur.pl
 http://www.sigaev.ru/gist/concur.sh
 
  BTW, these scripts seem to indicate that there's a GIST or
  contrib/intarray problem in the 8.0 branch.  I was trying to use 'em
  to test REL8_0_STABLE branch tip to verify my t_ctid chain backpatch,
  and I pretty consistently see Problem with update:
 
  Start: parallel mode with 4 flows
  Problem with update {77,77}:0 count:1 at concur.pl line 91.
  Issuing rollback() for database handle being DESTROY'd without explicit
  disconnect(). Problem with update {43,24}:3 count:1 at concur.pl line 91.
  Issuing rollback() for database handle being DESTROY'd without explicit
  disconnect(). Problem with update {43,43}:2 count:1 at concur.pl line 91.
  Issuing rollback() for database handle being DESTROY'd without explicit
  disconnect(). 1 flow finish. Stats: ni:75000 nu:1661 nd:216 nv:13(nf:3)
  nt:780 All flow finish; status: 255; elapsed time: 265.48 sec
 
  Is this something that can be fixed for 8.0.4?
 
  regards, tom lane

Since 7.4 we have troubles with ltree (seldom corruption of buffer cache, not 
on-disk), might this bug be somehow related to the ltree problem?
7.2 was rock-stable with ltree.

Best regards,
Mario Weilguni

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

Re: VACUUM/t_ctid bug (was Re: [HACKERS] GiST concurrency commited)

[Mario Weilguni]

Am Dienstag, 30. August 2005 12:19 schrieb Teodor Sigaev:
  Since 7.4 we have troubles with ltree (seldom corruption of buffer cache,
  not on-disk), might this bug be somehow related to the ltree problem? 7.2
  was rock-stable with ltree.

 Not sure. Fixed bug was (@ - contains operation):

 update wow set a = a || '{101}'::int[] where a @ '{1,2,3}';
 select a from wow where a @ '{1,2,3}' and not a @ '{101}';

 After update query select must not find any rows, but it did. The problem
 was in GiST code and so any GiST idexes was affected.

 Can you say more about your trouble?

We have queries that use ltree for sorting too, the sort looks like this:
  order by subpath(ltreefield, 0, nlevel(ltreefield) - 1)

But concurrency leads to a bug, that results in an sql-error: 
ERROR:  invalid positions

Now we use locking to prevent concurrenct access on the most-used concurrent 
part of the program, and the problem is extremly rare now, I had only 4 
occurences in one year, but still happens (there are other access paths that 
do not use locking, but they are rareley accessed). 

It seems the ltree length parameter is set to 0 in the tuples, the content 
itself is still there: Example:
Say the tuple was before treefield='1.2.3.4.5'
After the occurence of the error, I get: treefield='' (empty, but not null)

Using a tool Tom Lane told me to use, I checked it, and on-disk I had still 
1.2.3.4.5, but the length parameter of the ltree column was 0 (sorry, I was 
wrong in my first mail, on-disk was broken too.)

Might this be somehow related to the intarray bugs?

Best regards,
Mario Weilguni


p.s.: I tried hard to create a self-contained test for tracking this down, but 
failed. 


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

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

Re: [HACKERS] 8.1 release notes

[Mario Weilguni]

JFYI:

Allow indexes to be used for MIN/MAX (Tom)

In previous releases, the only way to use index for MIN/MAX was to
rewrite the query as SELECT col FROM tab ORDER BY col LIMIT 1. This not
happens automatically.

I guess this should read This now happens automatically

Regards,
Mario Weilguni

-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of Oleg Bartunov
Sent: Tuesday, August 23, 2005 9:04 AM
To: Bruce Momjian
Cc: PostgreSQL-development
Subject: Re: [HACKERS] 8.1 release notes

Bruce,

I think I took part in GiST concurrency and recovery project.

Oleg
On Mon, 22 Aug 2005, Bruce Momjian wrote:

 I have compiled the 8.1 release notes and converted them to SGML at:

  
 http://candle.pha.pa.us/main/writings/pgsql/sgml/release.html#RELEASE-
 8-1

 I still need to add markup and cleanup, but it is good enough for 
 review and for beta1.  I will work on it more tomorrow.



Regards,
Oleg
_
Oleg Bartunov, sci.researcher, hostmaster of AstroNet, Sternberg
Astronomical Institute, Moscow University (Russia)
Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/
phone: +007(095)939-16-83, +007(095)939-23-83

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

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

Re: [HACKERS] pg_get_prepared?

[Mario Weilguni]

Am Freitag, 15. Juli 2005 14:19 schrieb Greg Sabino Mullane:
  The use case is when you want to prepare a query, but only if it's not
  already prepared on that connection.

 This has been covered before, but to reiterate: why would you need this?
 Any application worth its salt should be tracking which statements it
 has already prepared (after all, they cannot span connections). Seems
 a waste of resources to make a separate call to the database for
 information you should already know.

Does not apply to mod_php/apache, you simply do not know if a connection made 
my pg_pconnect is a new connection or a reused one. That has nothing to do 
with the application itself.


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

Re: [HACKERS] Usernames with hyphens

[Weilguni Mario]

In fakt, it allows users with hyphens, but you have to use quotes:
CREATE USER foo-bar; 
DROP USER foo-bar; 

Regards,
Mario Weilguni

-Ursprüngliche Nachricht-
Von: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Im Auftrag von Devrim GUNDUZ
Gesendet: Montag, 13. Juni 2005 13:31
An: pgsql-hackers@postgresql.org
Betreff: [HACKERS] Usernames with hyphens

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1


Hi,

AFAIK, PostgreSQL does not allow hyphens in usernames and databases, right?

template1=# SELECT version();
version
- 
--
  PostgreSQL 8.0.3 on i686-redhat-linux-gnu, compiled by GCC gcc (GCC)
3.2.3 20030502 (Red Hat Linux 3.2.3-42)
(1 row)

template1=# CREATE DATABASE hyphen-test;
ERROR:  syntax error at or near - at character 22 LINE 1: CREATE DATABASE 
hyphen-test;
  ^
This is the same for users;

template1=# CREATE USER hyphen-test;
ERROR:  syntax error at or near - at character 18 LINE 1: CREATE USER 
hyphen-test;
  ^
However, createuser allows us to create users that include a hyphen:

# createuser hyphen-test -U postgres
Shall the new user be allowed to create databases? (y/n) n Shall the new user 
be allowed to create more new users? (y/n) n
Password:
CREATE USER

but it is not allowed in CREATE DATABASE syntax:

template1=# CREATE DATABASE hyphen_test with owner hyphen-test;
ERROR:  syntax error at or near - at character 46 LINE 1: CREATE DATABASE 
hyphen_test with owner hyphen-test;


template1=# SELECT * from pg_shadow WHERE usename='hyphen-test';
usename   | usesysid | usecreatedb | usesuper | usecatupd | passwd | 
valuntil | useconfig
- 
-+--+-+--+---++--+---
  hyphen-test |  103 | f   | f| f || 
|
(1 row)

Is it a bug in createuser or am I missing a point? Same applies to
createdb:

# createdb hyphen-test -U postgres
Password:
CREATE DATABASE

Regards,

- --
Devrim GUNDUZ
devrim~gunduz.org, devrim~PostgreSQL.org, devrim.gunduz~linux.org.tr
http://www.tdmsoft.com.tr  http://www.gunduz.org
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.2.1 (GNU/Linux)

iD8DBQFCrW5dtl86P3SPfQ4RAuF5AJ4xGGqswtNdzcLGi/lNCo8hD5PsYgCggllm
u5GYDj/JODlZ5HA+XYD79DM=
=pFsg
-END PGP SIGNATURE-

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

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

[HACKERS] overlaps() does not work as expected?

[Mario Weilguni]

I've quite some trouble with the overlaps function:
SELECT overlaps('9.6.2005'::date, '9.6.2005'::date, '9.6.2005'::date, 
'9.6.2005'::date);
returns true (these are german timestamps dd.mm.)

SELECT overlaps('8.6.2005'::date, '9.6.2005'::date, '9.6.2005'::date, 
'9.6.2005'::date); 
returns false

Is this a bug or a (quite strange) feature?

Thanks!

---(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] ORDER BY different locales for 8.0

[Mario Weilguni]

Am Dienstag, 19. April 2005 09:18 schrieb Honza Pazdziora:
 Hello,

 the nls_string function that makes it possible to sort by arbitrary
 locale has been updated to reflect the changes in error handling in
 PostgreSQL 8.0, due to users using the nls_string sorting on 7.4 and
 requesting it for 8.0 as well. The distribution can be downloaded from

   http://www.fi.muni.cz/~adelton/l10n/
  
 http://www.fi.muni.cz/~adelton/l10n/postgresql-nls-string/postgresql-nls-st
ring-8.01.tar.gz

 I'll appreciate any comments.

 The README is as follows:

 -
 Why this function:

 PostgreSQL, at least until version 8.0, has rather weak support for
 various collating sequences -- what you get when you do

   select ... order by column.

 The sorting is closely tied to indexes used throughout the database
 cluster and is specified by locale settings at the initdb time.
 Yet, people asked for ways of specifying the collating rules at runtime,
 even if the sorting will not use indexes. Just take the records and
 sort them. It is reasonable request to want one select to order by
 using English rules, another one to run with German rules and yet
 another with Czech ones, without having to dump, initdb, restore.

Cool. I'm working on a multi-lingual database, so this is exactly what I 
looked for. The only disatvantage is the length of the resulting strings, 
it's around 9 time longer than the original string.

Just a suggestion:
Could it be done better by not returing a string, but a non-locale affected 
representation like some sort of byte array, so the size could be reduced 
alot? 

However, extremly useful. Thanks!


---(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] Raise Exception

[Mario Reis]

  Dear Sir,

 I' ve recently join to PostGreSql community. I'm testing it on a local
network and I'm very found of it .
However there are a few things that I'd like to understand better.

 As far as i realise, every time the Server validates a wrong value for an
input it Raises an Exception with the check failure for each input for each
record
 For example, for each invalid foreing key, it automaticly raises an
exception.If you
have a large file 20fields to validate before insert the validates each on
at a time and
raises an exception for each falure.
 As far as i understand it should join all  the validities from each field
record and latter
 display/notify, all them at once, joinning all the errors after an insert
or update instruction
 for each record of course.
 This way it w'll save time and resouces communications special in a large
network with a large
 number of users.

 Sorry if i got it wrong. I also appologise if this isn't the right place to
put this question but
i don't know where else puting it.

 I hope you w'll understand what i mean. Sorry for my poor english.

 Thanks any way

 Mário


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

Re: [HACKERS] LinuxTag wrapup

[Mario Weilguni]

 
 Because their SQL queries always seem to need a target object to select 
 from.  i.e. SELECT NEXTVAL.foo isn't valid for Oracle 8/9.
 

It has been a long time since I've used Oracle, but shouldn't it be select 
foo.nextval from dual?

Regards,
Mario Weilguni

---(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] Tool to read data files

[Mario Weilguni]

Are there any tools available to dump datafiles for debugging purposes? I think there 
is a corruption problem in Postgresql 7.4.2 with and only with the Ltree module. 
I've seen broken ltree entries in my database, and there were no updates on the 
tables. This occurs once or twice a day, at random times. The value of the ltree
attribute is not junk as I would expect with a corruption, but some parts are missing.

I've wrote some sort of watchdog and waited until the problem occured, then copied the 
datafile to a different location.

Are there any tools available for debugging such problems? Even a simple dump to 
stdout would be helpful.

Thanks!

Regards,
Mario Weilguni

p.s. I've checked the hardware before going online with that database, RAM is 
definitly ok, and the rest of the hardware should work fine as well. 
The table was vacuumed full, but vacuum did not report any errors.


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

   http://archives.postgresql.org

Re: [HACKERS] Call for 7.5 feature completion

[Mario Weilguni]

Am Tuesday 18 May 2004 07:40 schrieb Greg Copeland:
 From the FAQ (http://www.drbd.org/316.html):
 
 Q: Can XFS be used with DRBD?
 
 
 A: XFS uses dynamic block size, thus DRBD 0.7 or later is needed.
 
 Hope we're talking about the same project.  ;)

Hmmm, interesting. But I did not find 0.7 on the history page 
http://www.drbd.org/releases.html
maybe it's not release status yet - thus no option for now.

Regards,
Mario Weilguni


---(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] Call for 7.5 feature completion

[Mario Weilguni]

 
 Interesting.
 We have made COMPLETELY different experiences.
 
 There is one question people ask me daily: When can we have sychronous 
 replication and PITR?.
 Performance is not a problem here. People are more interested in 
 stability and enterprise features such as those I have mentioned above.

I doubt that. Having deployed several 7.4 databases, the first customers ask 
(of course not in technical speech, but in the meaning) when the problem with 
checkpoint hogging system down is solved. This is a really serious issue, 
especially when using drbd + ext3. The system will become really unresponsive 
when checkpoint is running.

I heavily await 7.5 because of the background writer.

---(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] Call for 7.5 feature completion

[Mario Weilguni]

Am Monday 17 May 2004 22:42 schrieb Jan Wieck:
  I doubt that. Having deployed several 7.4 databases, the first customers ask 
  (of course not in technical speech, but in the meaning) when the problem with 
  checkpoint hogging system down is solved. This is a really serious issue, 
  especially when using drbd + ext3. The system will become really unresponsive 
  when checkpoint is running.
  
  I heavily await 7.5 because of the background writer.
 
 Have you done some more extensive tests with 7.5 already and if so, what 
 are your experiences with it so far?

Not really yet, I've installed 7.5 on a development machine yesterday, changed to JFS 
filesystem, and so
far the system feels more responsive, but I've yet to test it. 7.5 on my personal PC 
performed very fine, especially
with some more problematic queries it produced better query plans.

Regards,
Mario Weilguni

---(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] Call for 7.5 feature completion

[Mario Weilguni]

 
 Well that seems to be part of the problem. ext3 does not scale well at 
 all under load. You should probably upgrade to a better FS (like XFS). I 
 am not saying that your point isn't valid (it is) but upgrading to a 
 better FS will help you.
 

Thanks for the info, but I've already noticed that. XFS is no option since it does not 
work with drbd,
but jfs seems to be quite good.

Regards,
Mario Weilguni

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

Re: [HACKERS] TODO list

[Mario Weilguni]

Am Tuesday 06 January 2004 21:30 schrieb Jon Jensen:
 On Tue, 6 Jan 2004, Andrew Dunstan wrote:
  Also, I would like to see some kind of session identifier that is more
  unique than pid, which wraps around.  Ideally we could have 10{pid},
  then then the pid wraps around, 20{pid), or something like that.
 
  This requires some thought. ISTM it wouldn't buy you much unless you
  made it persistent across server restarts, and possibly not even then.

 And on OpenBSD (though no other platforms that I know of) the PID is a
 random number, so there is no wrapping to begin with.

Linux = 2.6 has random pids too.

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

[HACKERS] Stupid index idea...

[Mario Weilguni]

Hi,

probably it's just a stupid idea, but what do you think of this:currently, 
most if not all queries with aggregates (count(), sum()) make seq scans 
when there are no conditions at all. especially count() is a classic question 
on any postgres list.

the reason is - at least that's what I got from this list - is the 
multi-version system postgres is using. Tom Lane once said that resolving 
this to use index scans would mean adding 8 bytes to every index tuples, 
which is no way to go. but what if 1 bit could be added to index items, 
indication that the heap tuples MAY be modified?

in any of my database applications updated tuples in the tables are seldom, 
because of vacuum. I've a table containing 10 million of tuples, and a 
count(1) on them takes several minutes. however, the table is quite static 
and changes seldom, and only a few items change.

so an extra bit in the index could indicate: this is a candiate, but the heap 
must be checked. in most of my applications this would mean that for 95% of 
all tuples the index can be used for aggregates, only the remaining 5% of all 
tuples that MIGHT be modified or deleted have to be checked via heap.
and even those 5% are often too much, in my case it's typically below 1%.

The statistic collector could even tell the optimizer if an index scan is the 
way to go...

Would that work?

Best regards,
Mario Weilguni



---(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] PostgreSQL Benchmarks

[Mario Weilguni]

Hrm.  I just saw that the PHP ADODB guy just published a bunch of database
benchmarks.  It's fairly evident to me that benchmarking PostgreSQL on
Win32 isn't really fair:

http://php.weblogs.com/oracle_mysql_performance

And why is the highly advocated transaction capable MySQL 4 not tested?
That's the problem, for every performance test they choose ISAM tables, and
when transactions are mentioned it's said MySQL has transactions. But why
no benchmarks?

Regards,
Mario Weilguni

---(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] interesting difference for queries...

[Mario Weilguni]

I noticed an interesting difference in query behaviour:

cms=# CREATE TABLE foo(bar int);
CREATE
cms=# SELECT * from foo where bar=1.7;
 bar
-
(0 rows)

cms=# SELECT * from foo where bar='1.7';
ERROR:  pg_atoi: error in 1.7: can't parse .7

Is this the same problem as index usage with/without quotes? However, one
would expect the same output from both queries, either the error message, or
better the 0 rows result. 

Regards
Mario Weilguni

---(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] possible obvious bug?

[Mario Weilguni]

I was playing with the Japanese win32 7.2.1 port and I noticed that select
0 / 0 caused the server to crash and restart.  I understand that it is a
totally unsupported version, but it should be easy enough to check vs. the
current version.  Note that select 0.0/0.0 worked fine!

Seems to work fine on my system.

postgres=# SELECT version();
   version
-
 PostgreSQL 7.2.1 on i686-pc-linux-gnu, compiled by GCC egcs-2.91.66
(1 row)

postgres=# SELECT 0/0;
ERROR:  floating point exception! The last floating point operation either
exceeded legal ranges or was a divide by zero
postgres=#



---(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] null values / partial indices

[Mario Weilguni]

I noticed that the planner is unable to select an index scan when a partial
index is available, the partial index is based on a NOT NULL condition.

Example:

start with no index:
mydb=# EXPLAIN ANALYZE select id from str where url='foobar';
NOTICE:  QUERY PLAN:

Seq Scan on str  (cost=0.00..88.91 rows=1 width=4) (actual time=5.93..5.93
rows=0 loops=1)
Total runtime: 6.01 msec

EXPLAIN
mydb=# create index str_idx_url on str(url) where url is not null;
CREATE
mydb=# analyze str;
ANALYZE
mydb=# EXPLAIN ANALYZE select id from str where url='foobar';
NOTICE:  QUERY PLAN:

Seq Scan on str  (cost=0.00..91.05 rows=3 width=4) (actual time=6.24..6.24
rows=0 loops=1)
Total runtime: 6.30 msec

EXPLAIN
mydb=# drop index str_idx_url;
DROP
mydb=# create index str_idx_url on str(url);
CREATE
mydb=# analyze str;
ANALYZE
mydb=# EXPLAIN ANALYZE select id from str where url='foobar';
NOTICE:  QUERY PLAN:

Index Scan using str_idx_url on str  (cost=0.00..2.56 rows=1 width=4) (actual
time=0.53..0.53 rows=0 loops=1)
Total runtime: 0.60 msec

EXPLAIN



It's no big deal in my application, speed is more than fast enough, I just
noticed it. The documentation says:
However, keep in mind that the predicate must match the conditions used in
the queries that are supposed to benefit from the index. To be precise, a
partial index can be used in a query only if the system can recognize that
the query's WHERE condition mathematically implies the index's predicate.
PostgreSQL does not have a sophisticated theorem prover that can recognize
mathematically equivalent predicates that are written in different forms.
(Not only is such a general theorem prover extremely difficult to create, it
would probably be too slow to be of any real use.) The system can recognize
simple inequality implications, for example x  1 implies x  2;
otherwise the predicate condition must exactly match the query's WHERE
condition or the index will not be recognized to be usable. 

Normally a IS NOT NULL/IS NULL should be easy to recognise, since NULL is
very special. This would allow much smaller indices in some applications, for
example I've a case with a table with 20 rows where 4 values (of type
text) are not null. The index size would be much smaller without all those
NULL values. 

Best regards,
Mario Weilguni


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

Re: [HACKERS] null values / partial indices

[Mario Weilguni]

You can try an index like:
create index str_idx_url on str(url) where url = ''; 

I think that should be identical. ('' is the smallest string, no ?)

Thanks alot, it works now. But I still think the NOT NULL case would be
useful.

Best regards,
Mario Weilguni

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

[HACKERS] Interesting VACUUM notice

[Mario Weilguni]

I found a message I've never seen before in VACUUM, its:
NOTICE:  Too old parent tuple found - can't continue repair_frag

The version is Postgresql 7.2.1.

The problem occurs in vacuum.c, around line 1700, but the interesting part is the 
comment around:
/*
* Read above about cases when
* !ItemIdIsUsed(Citemid) (child item is
* removed)... Due to the fact that at the moment
* we don't remove unuseful part of update-chain,
* it's possible to get too old parent row here.
* Like as in the case which caused this problem,
* we stop shrinking here. I could try to find
* real parent row but want not to do it because
* of real solution will be implemented anyway,
* latter, and we are too close to 6.5 release. -
* vadim 06/11/99
*/

This sounds like a solution should be available, but it seems to happen anyway. 
Yesterday I've found no way to fix this problem,
but today it's not reproduceable any more. Might this notice indicate a serious 
problem?

Best regards,
Mario Weilguni


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

[HACKERS] Fwd: [GENERAL] index not scanned

[Mario Weilguni]

The topic below is quite common on the general list, people tend to ask without 
checking the documentation. I think most of those questions will
disappear if the planner output is modified, so it is clear why a decision is made 
(maybe with an extra option).

e.g. something like this if an index scan is possible but not used:

EXPLAIN SELECT * FROM copy_of_forum_list_child WHERE f_id = 1 and
father_name = 'top';
Seq Scan on copy_of_forum_list_child  (cost=0.00..2.44 rows=1width=100, costs using 
index: 0.00...9.44 rows=... with=...)

Any comments?

Regards,
Mario Weilguni


--  Weitergeleitete Nachricht  --

Subject: [GENERAL] index not scanned
Date: Sun, 27 Oct 2002 12:46:39 +0200
From: Ben-Nes Michael [EMAIL PROTECTED]
To: postgresql [EMAIL PROTECTED]

Hi

I add an Index to table that have only 30 rows.

CREATE INDEX copy_of_forum_l_c_f_id_idx ON copy_of_forum_list_child ( f_id,
father_name );

when i do:
EXPLAIN SELECT * FROM copy_of_forum_list_child WHERE f_id = 1 and
father_name = 'top';

its return: Seq Scan on copy_of_forum_list_child  (cost=0.00..2.44 rows=1
width=100)

why its not scanning the index ?

does the planner knows that the table is small and it will take more time to
check the index then stright check ?

by the way, the table will get quite big in the future.

Cheers


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

---


---(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] number of attributes in page files?

[Mario Weilguni]

Is it possible to get rid of the t_natts fields in the tuple header? Is this field 
only for alter table add/drop support? Then it might
possible to get rid of it and put the t_natts field in the page header, not the 
tuple header, if it can be assured that when updating/inserting
records only a compatible (a page file with the same number of attributes) page file 
is used. Especially master-detail tables would 
profit from this, reducing the tuple overhead by another 9%.

Might this be possible?

Regards,
Mario Weilguni




---(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] number of attributes in page files?

[Mario Weilguni]

Am Freitag, 11. Oktober 2002 14:12 schrieb Tom Lane:
 Mario Weilguni [EMAIL PROTECTED] writes:
  Is it possible to get rid of the t_natts fields in the tuple header?
  Is this field only for alter table add/drop support?

 Only?  A lot of people consider that pretty important ...

With only I mean it's an administrative task which requires operator intervenation 
anyways, and it's a seldom needed operation which may take longer, when
queries become faster.


 But removing 2 bytes isn't going to save anything, on most machines,
 because of alignment considerations.

ok, I did not consider alignment, but the question remains, is this easily doable? 
Especially because only one another byte has to be saved for
real saving on many architectures, which is t_hoff. IMO t_hoff is not useful because 
it can be computed easily. This would give 20 byte headers instead of 23 (24) bytes as 
it's now. 
This is 17% saved, and if it's not too complicated it might be worth to consider.

Best regards,
Mario Weilguni

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

http://archives.postgresql.org

Re: [HACKERS] (Fwd) Re: Any Oracle 9 users? A test please...

[Mario Weilguni]

Tom Lane wrote:
  

Has anyone done the corresponding experiments on the other DBMSes to
identify exactly when they allow CURRENT_TIMESTAMP to advance ?


This applies up to Oracle 8.1.6, maybe it helps:
According to a co-worker, Oracle advances the time in transactions:
select to_char(sysdate, 'dd.mm. hh24:mi:ss') from dual;

TO_CHAR(SYSDATE,'DD
---
03.10.2002 10:16:28

(wait ...)

SQL r
  1* select to_char(sysdate, 'dd.mm. hh24:mi:ss') from dual

TO_CHAR(SYSDATE,'DD
---
03.10.2002 10:17:41


It even advances within procedures/functions, example:

 create or replace procedure foobar is 
   s1 varchar(2000);
   s2 varchar(2000);
 begin
   select to_char(sysdate, 'dd.mm. hh24:mi:ss') into s1 from dual;
   (... put long running query here ...)
   select to_char(sysdate, 'dd.mm. hh24:mi:ss') into s2 from dual;
   dbms_output.put_line(s1);
   dbms_output.put_line(s2);
 end; 
/

set serverout on
execute foobar;


Hope it helps.

Regards,
Mario Weilguni

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

Re: [HACKERS] pg_dump and large files - is this a problem?

[Mario Weilguni]

My limited reading of off_t stuff now suggests that it would be brave to 
assume it is even a simple 64 bit number (or even 3 32 bit numbers). One 
alternative, which I am not terribly fond of, is to have pg_dump write 
multiple files - when we get to 1 or 2GB, we just open another file, and 
record our file positions as a (file number, file position) pair. Low tech, 
but at least we know it would work.

Unless anyone knows of a documented way to get 64 bit uint/int file 
offsets, I don't see we have mush choice.

How common is fgetpos64? Linux supports it, but I don't know about other
systems.

http://hpc.uky.edu/cgi-bin/man.cgi?section=alltopic=fgetpos64

Regards,
Mario Weilguni

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