[GENERAL] PickSplit method of 2 columns ... error

[Kevin Neufeld]

Has anyone come across this error before?

LOG:  PickSplit method of 2 columns of index 
'asset_position_lines_asset_cubespacetime_idx' doesn't support secondary 
split


This is a multi-column GiST index on an integer and a cube (a data type 
from the postgres cube extension module).


I traced the error to the gistUserPicksplit 
 function in the 
gistsplit.c ... I surmise that this method is called whenever a page 
split is necessary.


So, I know when this error occurs, but I don't know why.

Thoughts anyone?
Cheers,
Kevin

--
Kevin Neufeld
Software Developer
Refractions Research Inc.
300-1207 Douglas St.
Victoria, B.C., V8W 2E7

Phone: (250) 383-3022
Email: [EMAIL PROTECTED]


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

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

Re: [GENERAL] Read Access to database

[Kevin Neufeld]

This seems unnecessarily complicated.

Yes, I believe you do have to grant select on every table, but you can 
use psql to generate the queries, then execute them.

i.e.

-- show only tuples
/t

-- output to temp script file.
/o script.sql

-- generate your script using pg_tables
SELECT 'GRANT SELECT ON ' || schemaname || '.' || tablename || ' TO d;' 
from pg_tables where schemaname = 'abcs';


-- stop writing to script file.
/o

-- run your script
/i script.sql

You can create a cron job that will do these sequence of commands 
nightly if you wish.


Hope this helps,
-- Kevin

--
Kevin Neufeld
Software Developer
Refractions Research Inc.
300-1207 Douglas St.
Victoria, B.C., V8W 2E7

Phone: (250) 383-3022
Email: [EMAIL PROTECTED]


A. Kretschmer wrote:

am  Tue, dem 28.08.2007, um 10:56:38 +0530 mailte Ashish Karalkar folgendes:
  

Hello all,
 
I have a database  abc  with owner c .

I want to grant only read access on this DB abc  to user d.
More specificaly to a schema abcs in the databse abc.
Is ther any way to do so?
 
I have more than 1000 table so dont want to list all the table name in the

grant command.



Okay:

http://www.archonet.com/pgdocs/grant-all.html

Andreas
  


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

Re: [GENERAL] PickSplit method of 2 columns ... error

[Kevin Neufeld]

Yes, thanx. This would be useful as some of our clients are getting 
swamped (and confused) with these messages in the log files.


Cheers,
Kevin

Tom Lane wrote:

Teodor Sigaev <[EMAIL PROTECTED]> writes:
  
Split page algorithm was rewrited for 8.2 for multicolumn indexes and API for 
user-defined pickSplit function was extended to has better results with index 
creation. But GiST can interact with old functions - and it says about this. 
That isn't mean some real problem or error - index will be the same as in 8.1, 
not better.



Perhaps that LOG message should be reduced to DEBUG1 or so.  It's not
like it's something the average user could fix for himself.

regards, tom lane

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


--
Kevin Neufeld
Software Developer
Refractions Research Inc.
300-1207 Douglas St.
Victoria, B.C., V8W 2E7

Phone: (250) 383-3022
Email: [EMAIL PROTECTED]


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

[GENERAL] System V IPC on Windows

[Kevin Neufeld]

Does anyone know how to adjust the IPC settings in Windows?

If I wanted to increase shared_buffers settings, in linux I would simply 
adjust the SHMMAX and SHMMIN settings, following the docs 
(http://www.postgresql.org/docs/8.2/static/kernel-resources.html#SYSVIPC).


In Windows, ?

Cheers,
Kevin

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

Re: [GENERAL] System V IPC on Windows

[Kevin Neufeld]

That makes sense, thanx. 

Another individual was having problems adjusting the shared_memory 
settings higher than 1.2GB on a 8GB 64bit machine running Vista.  
Whenever he would adjust higher than that, the postgresql service 
wouldn't start throwing some kind of error.  In linux, one would simple 
adjust the SHMMAX settings.  If there's no such setting in Windows, then 
the problem must lie somewhere else.


On a side, the docs could be a little more clear on this.  
(http://www.postgresql.org/docs/8.2/static/kernel-resources.html)
The only reference to Windows says "(For the Windows port, PostgreSQL 
provides its own replacement implementation of these facilities)".


Cheers,
Kevin

Magnus Hagander wrote:

 > > Does anyone know how to adjust the IPC settings in Windows?
  
There aren't any such settings in Windows, AFAIK. 



Correct. The only real adjustable limit is the size of the Windows pagefile, 
but that one is normally dynamic. But there must be room for all the shared 
memory in it. It's not going to be there, but the space is reserved.

That said, if you need to increase the pagefile size to accomodate your shared 
buffers, you likely have way too large value for shared buffers.

/Magnus
  


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

Re: [GENERAL] System V IPC on Windows

[Kevin Neufeld]

 > > Does anyone know how to adjust the IPC settings in Windows?


There aren't any such settings in Windows, AFAIK.
  

Correct. The only real adjustable limit is the size of the Windows
pagefile, but that one is normally dynamic. 

[Lee Keel] 


What if the page file exceeds the shared_buffers, but you can't increase the
shared buffers to a larger amount?  For example, page file is set to be
between 500MB and 10GB, but you can't set the shared_buffers to more than
1200MB.  If set to 1300MB or higher then service will not start.

Please see "GEOS union() error" thread for more details.

Thanks,
Lee
  
(In the postgis-users list 
http://postgis.refractions.net/pipermail/postgis-users/2007-November/017616.html)


-- Kevin

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

[GENERAL] table creation/modified timestamp

[Kevin Neufeld]

A wish-list feature for me is for postgresql to keep a record of a table 
creation date and modified date.  This could be added to the '\dt+' 
shortcut.


I have well over 100 tables and I find I'm always asking myself when 
certain tables were created.  I realize I could always look at the 
relfilenode attribute from pg_class and hunt around in the filesystem to 
track down the datestamp the OS gave the file ... but a shortcut view 
would be nice.


Cheers,
Kevin

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

Re: [GENERAL] function cache effect still happening?

[Kevin Neufeld]

Hi Fernando,

I ran into something similar ... with hard-coded queries in a function 
that ends up getting cached.  My solution was to store the referenced 
table in a variable and, as you suggested, use EXECUTE to dynamically 
build up and run the query.


Cheers,
Kevin


Fernando Moreno wrote:
2008/5/26 Gurjeet Singh <[EMAIL PROTECTED] 
>:


On Mon, May 26, 2008 at 9:49 PM, Fernando Moreno
<[EMAIL PROTECTED] > wrote:

Hi everyone, a few months ago I was still using Postgresql 8.2
and had the problem described here:
http://www.postgresql.org/docs/faqs.FAQ.html#item4.19 , that
time I solved it using EXECUTE for all sentences accessing
temporary tables. Right now I'm using 8.3,  the scenario is a
little different but the problem is the same. I have many
schemas with the same structure (tables, views and one
trigger), and two functions in the public schema which insert
and delete data from them, the INSERT and DELETE sentences are
hard-coded. Every schema represents a store from the same company.

The idea is that just by changing the search_path value to
something like "schema1,public", it's possible to execute the
functions and to process data for any schema (one at a time).
But the problem is here: through the client app, a user
invokes one of these functions on a given schema (schema1),
then requests a "store change", actually setting the
search_path to use another schema (schema2) and again,
executes any of the functions that access the schema tables,
BUT the function seems to be still linked to the first schema,
so new records are added to the wrong schema and delete
operations don't find the right record. EXECUTE will save the
day again, but I'd like to know if this is considered a known
bug even when it was apparently fixed.


I don't think it can be categorized as a bug! This is happening
because all the DML queries are prepared upon first execution, and
the plan stores the unique identifiers (OIDs) of the objects and
not the names of the objects. Upon changing search_path, the
function cache is not flushed, and hence the query plans are still
operating on the same objects.

I see two possibilities,

i) Flush function cache (only the query plans, if possible) when
changing search_path.
ii) Give users the ability to flush the function cache at will.

I don't think (ii) will have much backing, but (i) does make some
sense.

Best regards,
-- 
[EMAIL PROTECTED]

[EMAIL PROTECTED] gmail | hotmail | indiatimes | yahoo }.com

EnterpriseDB http://www.enterprisedb.com

Mail sent from my BlackLaptop device 




Thanks for your reply. I've been digging the list archive and I think 
EXECUTE is the best workaround, at least better than restarting the 
connection, creating the function again or restarting the server (!!). 
By the way, this flushing-function-cache thing seems to be an almost 
esoteric topic, because I wasn't able to find anything clear, unless 
you were talking about it more as a consequence than an action by itself.


On the other hand, perhaps this problem could have been avoided by 
creating the same function in every schema. That way the function 
cache and query plans would be harmless. Am I right?


Cheers. 


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

[GENERAL] contrib catalogs

[Kevin Neufeld]

This might seem like a silly question, but what are the implications of 
PostgreSQL allowing developers to create custom catalogs?


For example, PostgreSQL currently uses the pg_catalog schema to store 
system catalogs / relations / functions / etc.  Has thought gone into 
extending the scope to allow contrib modules to create their own 
catalogs (ie. a PostGIS catalog, cube, tsearch2, etc.)?


The largest advantage I see is significantly easier upgrades.  Having 
contrib tables/functions flagged as a catalog, pg_dump won't include 
them in a dump - restoring into an upgraded database becomes painless.


Cheers,
Kevin

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

Re: [GENERAL] contrib catalogs

[Kevin Neufeld]

Really?  I didn't know that ... guess I never tried.  I'll have to do 
some experimenting!  Thanx Tom.

-- Kevin

Tom Lane wrote:

Kevin Neufeld <[EMAIL PROTECTED]> writes:
This might seem like a silly question, but what are the implications of 
PostgreSQL allowing developers to create custom catalogs?


For example, PostgreSQL currently uses the pg_catalog schema to store 
system catalogs / relations / functions / etc.  Has thought gone into 
extending the scope to allow contrib modules to create their own 
catalogs (ie. a PostGIS catalog, cube, tsearch2, etc.)?


A superuser can create whatever he wants in pg_catalog.  Whether this
is a good idea or will behave smoothly is a topic that has not been
thought about, to my knowledge.

regards, tom lane



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

Re: [GENERAL] contrib catalogs

[Kevin Neufeld]

Tom Lane wrote:



A superuser can create whatever he wants in pg_catalog.  Whether this
is a good idea or will behave smoothly is a topic that has not been
thought about, to my knowledge.

regards, tom lane





Sorry, Tom. I think you are mistaken.  In my 8.3 instance, system 
catalog modifications are not allowed.


/opt/pgsql83/bin/psql -p 5432 -U postgres postgres

Welcome to psql 8.3.3, the PostgreSQL interactive terminal.

Type:  \copyright for distribution terms
   \h for help with SQL commands
   \? for help with psql commands
   \g or terminate with semicolon to execute query
   \q to quit

postgres=# \du
   List of roles
 Role name | Superuser | Create role | Create DB | Connections | Member of
---+---+-+---+-+---
 postgres  | yes   | yes | yes   | no limit| {}


postgres=# CREATE TABLE pg_catalog.test (id integer);
ERROR:  permission denied to create "pg_catalog.test"
DETAIL:  System catalog modifications are currently disallowed.


It would be interesting to test a contrib module install in pg_catalog 
though.

Cheers,
-- Kevin

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

Re: [GENERAL] contrib catalogs

[Kevin Neufeld]

Thanx for tip, Tom.  I'll definitely give that a try in my spare time.
Cheers,
Kevin

Tom Lane wrote:

Kevin Neufeld <[EMAIL PROTECTED]> writes:

Tom Lane wrote:

A superuser can create whatever he wants in pg_catalog.  Whether this
is a good idea or will behave smoothly is a topic that has not been
thought about, to my knowledge.


Sorry, Tom. I think you are mistaken.  In my 8.3 instance, system 
catalog modifications are not allowed.


Hm, I had forgotten that the allow_system_table_mods filter extends to
anything at all in pg_catalog.  But you could easily enable that setting
in a test database and then see what happens.

regards, tom lane


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