Re: [GENERAL] bug?: permission denied for schema on "on delete set null"

On Tue, 10 Mar 2009 13:12:03 -0400 Tom Lane wrote: > Ivan Sergio Borgonovo writes: > > I get a > > Query failed: ERROR: permission denied for schema user_test > > CONTEXT: SQL statement "UPDATE ONLY > > "user_test"."shop_commerce_baskets" SET

Re: [GENERAL] bug?: permission denied for schema on "on delete set null"

t;group" permissions assigned to the single users -- Ivan Sergio Borgonovo http://www.webthatworks.it -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] bug?: permission denied for schema on "on delete set null"

commerce_baskets ( sid int references sessions (sid) on delete set null, ... ); I'm on PostgreSQL 8.3.6 (Debian Lenny). thanks -- Ivan Sergio Borgonovo http://www.webthatworks.it -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] in role, ownership and permissions was: grant everything on everything and then revoke

On Tue, 03 Mar 2009 09:29:17 -0800 John R Pierce wrote: > Ivan Sergio Borgonovo wrote: > > I'd like to have different users mainly to have a different > > search schema path. > > Things may evolve so this is not going to be the only reason to > > have more than

Re: [GENERAL] What is the difference?

a15c+fb4e7219ab898ce+b08586cb81059f9','+',''',''') > || E'\'' and '95b5a221aeba15c','fb4e7219ab898ce','b08586cb81059f9'? You assume that the statement is interpreted twice. The first is comparing yo

[GENERAL] merging 2 databases

till working in particular) or if there are other approaches. -- Ivan Sergio Borgonovo http://www.webthatworks.it -- 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] grant everything on everything and then revoke

On Tue, 03 Mar 2009 09:29:17 -0800 John R Pierce wrote: > Ivan Sergio Borgonovo wrote: > > I'd like to have different users mainly to have a different > > search schema path. > > Things may evolve so this is not going to be the only reason to > > have more than o

[GENERAL] grant everything on everything and then revoke

h a few exception so it is easier to revoke than to grant. Any advice even with completely different approach? thanks -- Ivan Sergio Borgonovo http://www.webthatworks.it -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.pos

Re: [GENERAL] where to divide application and database

On Fri, 20 Feb 2009 20:45:20 + Sam Mason wrote: > On Fri, Feb 20, 2009 at 04:51:33PM +0100, Ivan Sergio Borgonovo > wrote: > > What I find a bit annoying is politely deal with the error once > > it is reported back to the application *and* connection and > > *ba

Re: [GENERAL] where to divide application and database

On Sat, 21 Feb 2009 15:02:55 -0800 Ron Mayer wrote: > Ivan Sergio Borgonovo wrote: > > I was wondering if "checks" may have an impact > > on performances and if pg does some optimisation over them. > Are you suggesting thee would be a positive or negative impact

Re: [GENERAL] where to divide application and database

pg types and the application language/library types it becomes easier to keep in sync those checks otherwise it is a really boring job and DB checks becomes just one more security net to maintain. In some places you REALLY appreciate/need that layer... sometimes it just get in the way. --

Re: [GENERAL] transfering tables into other schema

move a table in a new schema? The manual says: Associated indexes, constraints, and sequences owned by table columns are moved as well. But at my eyes I still can grasp the extent of the change. So I wouldn't like to be bitten by something I didn't take into account. -- Ivan Sergio Bor

Re: [GENERAL] transfering tables into other schema

On Tue, 17 Feb 2009 17:36:32 + Sam Mason wrote: > On Tue, Feb 17, 2009 at 06:20:54PM +0100, Ivan Sergio Borgonovo > wrote: > > I can't get how this really work. > > You're saying that constraint, fk/pk relationships will be > > preserved automaticall

Re: [GENERAL] transfering tables into other schema

ly... what else? OK BEFORE: create table x ( xid primary key, ... ); create table y ( xid int referencex x (xid), ... ); -- following in application select x.a, y.b from x join y on x.xid=y.xid; -- following in the DB create or replace function xy() as $$ begin select x.a, y.b from x join y on x.xid

[GENERAL] transfering tables into other schema

rd one day down. Of course running a well crafted set of SQL statement may do... But sed and plain text backup may do as well. Any other options? Any advices on how to procede once an option is the clear winner? thanks -- Ivan Sergio Borgonovo http://www.webthatworks.it -- Sent via pgsql-general

[GENERAL] idle DB and resources

move it on another box or leave it there since once the editors job will be finished the DB will be much smaller. Meanwhile we will have 2 large DB, one of them being nearly idle. Is the idle DB going to have any impact on performance? thanks -- Ivan Sergio Borgonovo http://www.webthatworks.it

Re: [GENERAL] [findings] minimal open source e-commerce software for pg

#x27;t dare to publish it till it will be clean enough I won't see a too fast improvement I can't handle on the project that originated all this. So, sooner or later there will be one more Free e-commerce project based on postgresql around. -- Ivan Sergio Borgonovo http://www.webtha

Re: [GENERAL] TSearch queries with multiple languages

m of good programmers ;) Not only Oleg write very valuable code, but he really cares about his users base. I'm still sorry I haven't been able to track down the origin of a very slow gin index creation I reported months ago. -- Ivan Sergio Borgonovo http://www.webthatworks.it -- Sent via pg

Re: [GENERAL] [findings] minimal open source e-commerce software for pg

.apache.org/ and actually I used it to learn something already... but well neither this fit the bill. thanks -- Ivan Sergio Borgonovo http://www.webthatworks.it -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.o

[GENERAL] [findings] minimal open source e-commerce software for pg

On Sat, 7 Feb 2009 01:43:10 +0100 Ivan Sergio Borgonovo wrote: > I need to build up a minimal e-commerce website on a host that is > already running postgresql. > > Requirement is minimal. Usual configurable pretty standard > couple of paying/shipping system and popular enough

[GENERAL] minimal open source e-commerce software for pg

has to be a quick and dirty job and the actual requirement are just the above. thanks -- Ivan Sergio Borgonovo http://www.webthatworks.it -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql

Re: [GENERAL] Need some help converting MS SQL stored proc to postgres function

able like [1] and create table as that seems the most promising for your needs http://www.postgresql.org/docs/8.3/interactive/sql-createtableas.html not everything is yet as we dream it, but there is still a lot of syntactic sugar available to exploit. -- Ivan Sergio Borgonovo http://www.webthatworks.

Re: [GENERAL] ssl to more than one server

On Thu, 29 Jan 2009 21:56:05 +0100 Ivan Sergio Borgonovo wrote: > On Thu, 29 Jan 2009 12:53:20 -0500 > Tom Lane wrote: > > > Ivan Sergio Borgonovo writes: > > > I succeded to connect to one postgresql server with ssl. > > > Now it's the time o

Re: [GENERAL] ssl to more than one server

On Thu, 29 Jan 2009 12:53:20 -0500 Tom Lane wrote: > Ivan Sergio Borgonovo writes: > > I succeded to connect to one postgresql server with ssl. > > Now it's the time of the second... but postgresql clients (pgsql) > > just look at ~/.postgresql/postgresql.(key|

[GENERAL] ssl to more than one server

e? thanks -- Ivan Sergio Borgonovo http://www.webthatworks.it -- 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] very long update gin index troubles back?

On Wed, 28 Jan 2009 23:38:47 +0300 (MSK) Oleg Bartunov wrote: > On Wed, 28 Jan 2009, Ivan Sergio Borgonovo wrote: > > On Tue, 27 Jan 2009 20:45:53 +0300 > > Teodor Sigaev wrote: > >>> No matter if I drop the trigger that update agg content and the > >

Re: [GENERAL] very long update gin index troubles back?

version should be index as old one. Does that mean that it could be a good choice to place the tsvector in another table? thanks -- Ivan Sergio Borgonovo http://www.webthatworks.it -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: ht

Re: [GENERAL] very long update gin index troubles back?

ve a gin index on agg. No matter if I drop the trigger that update agg content and the fact that I'm just updating d, postgresql will update the index? Right? -- Ivan Sergio Borgonovo http://www.webthatworks.it -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To

[GENERAL] very long update gin index troubles back?

nd create. Is there anything wrong in the above to make this update so slow on a 2x Xeon 3.2GHz 4GbRAM and a RAID1 [sic] I know it is slow on write. -- Ivan Sergio Borgonovo http://www.webthatworks.it -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to you

Re: [GENERAL] left join with smaller table or index on (XXX is not null) to avoid upsert

On Sun, 18 Jan 2009 22:12:07 +0100 Ivan Sergio Borgonovo wrote: > I've to apply a discounts to products. > > For each promotion I've a query that select a list of products and > should apply a discount. > > Queries may have intersections, in these intersections the

Re: [GENERAL] How can I look at a recursive table dependency tree?

On Mon, 19 Jan 2009 14:41:12 + Richard Huxton wrote: > Ivan Sergio Borgonovo wrote: > > On Mon, 19 Jan 2009 14:19:51 + > > Richard Huxton wrote: > > > >> Igor Katson wrote: > >>> Is there a way to watch all dependencies recursively without >

Re: [GENERAL] How can I look at a recursive table dependency tree?

to watch all dependencies recursively without > > doing a drop? > BEGIN; > DROP CASCADE... > -- check things > ROLLBACK; Isn't it going to be a pretty expensive way to see? Is the default log level enough to take note of the things that will be touched? (including eg

Re: [GENERAL] left join with smaller table or index on (XXX is not null) to avoid upsert

EAST. Since few products will get out of stock concurrently I'll have to regenerate the entries just for those product... with a rule or a trigger. I'm still looking for advices for an overall better strategy or just to lower the numbers of actual tests to see if this stuff is feasible in a decent time. -- Ivan Sergio Borgonovo http://www.webthatworks.it -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] too smart update (was: left join with smaller table or index on (XXX is not null) to avoid upsert)

e same field again to 4. With the trigger some But what should be the expected/standard behaviour? It seems that unless an update should fire triggers just if columns get updated... things will start to be a bit non-deterministic. You'll have to take into account rules etc... eg. FOUND is set tru

[GENERAL] left join with smaller table or index on (XXX is not null) to avoid upsert

f discounts easier, but I was wondering if it makes retrieval of Products and Prices slower. Having a larger table that is being updated at a rate of 5% to 10% a day may make it a bit "fragmented". Advices on the overall problem of discount overlap management will be appreciated t

HTML email (was: Re: [GENERAL] Vacuum and Reindex hangs)

w.webthatworks.it/d1/node/page/are_geeks_rich_media_impaired_or_why_html_emails_are_evil -- Ivan Sergio Borgonovo http://www.webthatworks.it -- 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] Polymorphic "setof record" function?

s, outside of the anyarray tricks. I still haven't got the time to use them, but wouldn't refcursor help? Unfortunately I didn't find very enlightening examples of refcursors use around. -- Ivan Sergio Borgonovo http://www.webthatworks.it -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] limit and other joined tables

on s.Aid=C.Cid; Can postgresql take advantage of the LIMIT even if it is in the outer select? -- Ivan Sergio Borgonovo http://www.webthatworks.it -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql

[GENERAL] refactoring: changing tables names

in any significant slowdown accessing the table indirectly? Later I'll have to rename tables that have associated sequences, pk/fk and are referenced in functions etc... etc... Any good list of advices? Is there any tools that works with postgresql that can help me? thanks -- Ivan S

[GENERAL] subselect and count (DISTINCT expression [ , ... ] ) performances

be spent on "distinct" where postgresql shouldn't suffer from its "count" implementation. But well still 300K rows to count on 1M aren't few. -- Ivan Sergio Borgonovo http://www.webthatworks.it -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) T

[GENERAL] WITH AS vs subselect was: count (DISTINCT expression [ , ... ] ) and documentation

On Fri, 26 Dec 2008 12:04:48 -0800 David Fetter wrote: > On Fri, Dec 26, 2008 at 08:03:30PM +0100, Ivan Sergio Borgonovo > wrote: > > > > aggregate_name (DISTINCT expression [, expression] ) > > > In 8.4, you'll be able to do: > > > WITH d AS ( >

Re: [GENERAL] count (DISTINCT expression [ , ... ] ) and documentation

On Fri, 26 Dec 2008 10:43:25 -0800 David Fetter wrote: > On Fri, Dec 26, 2008 at 03:34:33PM +0100, Ivan Sergio Borgonovo > wrote: > > I noticed that starting from 8.2 the documentation at > > http://www.postgresql.org/docs/8.2/interactive/sql-expressions.html > > sa

Re: [GENERAL] count (DISTINCT expression [ , ... ] ) and documentation

On Fri, 26 Dec 2008 16:23:52 +0100 "Pavel Stehule" wrote: > 2008/12/26 Ivan Sergio Borgonovo : > > On Fri, 26 Dec 2008 15:46:48 +0100 > > "Pavel Stehule" wrote: > > > >> count has only one argument, > > > > then what was changed bet

Re: [GENERAL] count (DISTINCT expression [ , ... ] ) and documentation

st values(1,0); insert into test.dist values(1,0); insert into test.dist values(1,1); insert into test.dist values(0,0); select count(*) from (select distinct a,b from test.dist ) a; but still I can't think of anything that would work with aggregate(distinct a,b) not just count. -- Ivan Ser

[GENERAL] count (DISTINCT expression [ , ... ] ) and documentation

expression) Still I'm using 8.3 and select count(distinct c1, c2) from table1; report: No function matches the given name and argument types. You might need to add explicit type casts. What should I write in spite of? select count(distinct c1, c2) from table1; -- Ivan Sergio Borgonovo

[GENERAL] return query and function result type must be real because of OUT parameters

and I get ERROR: function result type must be real because of OUT parameters ** Error ** ERROR: function result type must be real because of OUT parameters SQL state: 42P13 same with text etc... -- Ivan Sergio Borgonovo http://www.webthatworks.it -- Sent via pgsql-general ma

Re: [GENERAL] How to Import Excel Into PostgreSQL database

QL 2000 to 2005 -- Ivan Sergio Borgonovo http://www.webthatworks.it -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] design & available tricks: traversing heterogeneous tree (table-level + linked list)

e future. So a nested set (mptt) to represent the hierarchy may not be optimal and wring a safe and *fast* import function may not be trivial. I was wondering if there is some cool feature or cool contrib (8.3) that could make the choice much easier to take or just some suggestion. tha

[SOLVED] Re: [GENERAL] from 2 keys to serial

On Sun, 14 Dec 2008 20:16:35 +0100 Ivan Sergio Borgonovo wrote: The cleanest solution I was able to find was to redefine the addresses_temp table so that it uses the same sequence as the _dest table. Some general design advices would be still welcome. > I've to import something whos

[GENERAL] from 2 keys to serial

#x27;d bet that the problem of transforming 2 keys into a serial is pretty common and I'm asking for any alternative more elegant way than the above. thanks -- Ivan Sergio Borgonovo http://www.webthatworks.it -- Sent via pgsql-general mailing list (pgsql-general@postgresql

Re: [GENERAL] create table as vs. create table like

On Fri, 12 Dec 2008 13:25:07 +0100 "A. Kretschmer" wrote: > In response to Ivan Sergio Borgonovo : > > I just noticed something I found "unexpected". > > > > CREATE TABLE LIKE let you specify DEFAULT and Co. > > CREATE TABLE AS doesn'

[GENERAL] create table as vs. create table like

I just noticed something I found "unexpected". CREATE TABLE LIKE let you specify DEFAULT and Co. CREATE TABLE AS doesn't. Is there a one step way to clone a table? -- Ivan Sergio Borgonovo http://www.webthatworks.it -- Sent via pgsql-general mailing list (pgsql-general@pos

Re: [GENERAL] case when evaluating else condition anyway?

On Wed, 26 Nov 2008 14:18:44 + Sam Mason <[EMAIL PROTECTED]> wrote: > On Wed, Nov 26, 2008 at 02:53:07PM +0100, Ivan Sergio Borgonovo > wrote: > > case when ''=extinput then null else extinput::timestamp end > > I'd tend to use nullif(extinput,'&

[GENERAL] case when evaluating else condition anyway?

string to null and everything else to a timestamp. Is there any cleaner functional way that doesn't involve prepared statement etc... since the whole exercise is caused by an null-impaired DB API (aka MySQLish). -- Ivan Sergio Borgonovo http://www.webthatworks.it -- Sent via p

[GENERAL] long vacuum full, gin index and unusually long delete

problems... for all the other activities the box seems to behave happily... including some other heavy weight activities where postgresql is involved. -- Ivan Sergio Borgonovo http://www.webthatworks.it -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make chan

Re: [GENERAL] transaction_read_only effects on performance, actual meaning (was: How to reduce impact of a query)

UES ('oats'), ('wheat'), ('beans'); > > SET transaction_read_only = 1; > > SELECT * FROM a; > > COMMIT; > > > > but it does. Interesting. Thank you for pointing it out. -- Ivan Sergio Borgonovo http://www.webthatworks.it -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] tracking down a warning

t how can I find the statement? Which is the right log config to tweak to get enough info to be able to use grep on my code base without producing 2Gb logs in 5min? BTW can this log config be tweaked dynamically? by connection etc... or just in postgres.conf? thanks -- Ivan S

Re: [GENERAL] still gin index creation takes forever

in on a 2x Xeon HT 3.2GHz, 4Gb RAM and SCSI RAID1. It's far from being a scientific measure. I'll try to do more experiments later to collect more data and see if it didn't happen by chance. -- Ivan Sergio Borgonovo http://www.webthatworks.it -- 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] still gin index creation takes forever

Yes... 6min compared to something that span a night and is far more than what I'm willing to wait to give an exact measure since it does look to last more than the box itself. Anyway... I'll try Teodor's trick to see if somehow it can circumvent the real cause and I'll try ev

Re: [GENERAL] still gin index creation takes forever

uot;;3920;4166 "del";3092;3281 "edizioni";2465;2465 "della";2292;2410 "m";2283;2398 "dell";2150;2281 "j";1967;2099 "d";1789;1864 "per";1685;1770 "longman";1671;1746 "le";1656;1736 "press";1687;

Re: [GENERAL] sequence not restarting in a for loop (actually semi-random numbers)

On Wed, 12 Nov 2008 11:17:03 -0500 Tom Lane <[EMAIL PROTECTED]> wrote: > Ivan Sergio Borgonovo <[EMAIL PROTECTED]> writes: > > insert into mytop (id,n) select id, nextval('tmp_seq') > > from biglist join mylist on biglist.id=mylist > > order b

[GENERAL] sequence not restarting in a for loop (actually semi-random numbers)

esult I was expecting? -- Ivan Sergio Borgonovo http://www.webthatworks.it -- 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] still gin index creation takes forever

On Tue, 11 Nov 2008 22:02:17 -0500 Tom Lane <[EMAIL PROTECTED]> wrote: > Ivan Sergio Borgonovo <[EMAIL PROTECTED]> writes: > > Any suggestion about how to track down the problem? > > What you are describing sounds rather like a > use-of-uninitialized-memory problem

[GENERAL] still gin index creation takes forever

ns mysteriously even when there is no process stealing CPU cycles. Anyway when I'm creating a gin index CPU use is very high staying constantly near 100%. Any suggestion about how to track down the problem? thanks [1] temporary but not strictly temp tables -- Ivan Sergio Borgonovo http:/

Re: [GENERAL] psql exit code

On Mon, 10 Nov 2008 10:56:57 + Richard Huxton <[EMAIL PROTECTED]> wrote: > Ivan Sergio Borgonovo wrote: > > I'm running: > > > > ON_ERROR_STOP="on" PGPASSFILE="/somewhere" psql dbname username > > -f script.sql > > (or alt

[GENERAL] psql exit code

I'm running: ON_ERROR_STOP="on" PGPASSFILE="/somewhere" psql dbname username -f script.sql (or alternatively http://www.webthatworks.it -- 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] Fulltext index

deas? Multicolumn indexes should get into 8.4. You may add a column tsvector and compute it with a trigger that chose the correct language when generating the tsvector. Then you'll have to pick up the correct language when you generate the tsquery in your search. http://www.sigaev.ru/gin/fastin

Re: [GENERAL] options for launching sql script asynchronously from web app

s. I don't like it... but I've done it before and I'm still alive. I just have to trigger an event that run asynchronously, avoid other events of the same kind are triggered while one is running and report back event status. thanks -- Ivan Sergio Borgonovo http://www.webthatworks.i

[GENERAL] options for launching sql script asynchronously from web app

I'd like to launch some sql script asynchronously from a web app and have some kind of feedback later. Some form of authentication would be a plus. Is there anything ready? -- Ivan Sergio Borgonovo http://www.webthatworks.it -- Sent via pgsql-general mailing list (pgsql-ge

Re: [GENERAL] Specifying text to substitute for NULLs in selects

;', and all NULLs will from then on be output as NA. > > The COPY option is closest to a generic setting, but doesn't work > with a select query, just a table dump. \copy (select ) to ... works. As written in my 2nd post. -- Ivan Sergio Borgonovo http://www.webthatworks.it

Re: [GENERAL] Specifying text to substitute for NULLs in selects

d input syntax for integer: "NA" furthermore... even if c1 was text you may end up in output like: 'NA' that will be hard to be discerned from a "normal" string. BTW I just discovered that COPY doesn't work on view. -- Ivan Sergio Borgonovo http://www.webthatwork

Re: [GENERAL] Specifying text to substitute for NULLs in selects

drop cascades to table test.test DROP SCHEMA everything clearly explained in the COPY manual: http://www.postgresql.org/docs/8.1/static/sql-copy.html -- Ivan Sergio Borgonovo http://www.webthatworks.it -- 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] gin creation and previous history of server

On Wed, 05 Nov 2008 10:53:38 -0500 Tom Lane <[EMAIL PROTECTED]> wrote: > Ivan Sergio Borgonovo <[EMAIL PROTECTED]> writes: > > Tom Lane <[EMAIL PROTECTED]> wrote: > >> Can you put together a self-contained test case that illustrates > >> this? &g

Re: [GENERAL] gin creation and previous history of server

On Tue, 04 Nov 2008 10:33:26 -0500 Tom Lane <[EMAIL PROTECTED]> wrote: > Ivan Sergio Borgonovo <[EMAIL PROTECTED]> writes: > > It seems that gin creation is triggering something nasty in the > > server that depends on previous history of the server. > > Can you p

Re: [GENERAL] COPY TO duplicates "\" signs

; D:\\Program Files\\BMC Software\\CONTROL-D\\wa/reports > == ==== == try to play with the ESCAPE AS and set it as '' if you really want to skip \. http://www.postgresql.org/docs/8.1/static/sql-copy.html -- Ivan Sergio Borgonovo http://www.webthatwork

[GENERAL] gin creation and previous history of server

tion succede it is definitively slower than 3x gist/gin index creation looks more cpu bounded than memory bounded. I'm checking if I made some mistake in other cfg parameters that may have some impact on index creation... Any further clue? -- Ivan Sergio Borgonovo http://www.webthatwor

Re: [GENERAL] gin index creation performance problems

On Mon, 03 Nov 2008 11:04:45 -0500 Tom Lane <[EMAIL PROTECTED]> wrote: > Ivan Sergio Borgonovo <[EMAIL PROTECTED]> writes: > > maintenance_work_mem is still untouched. What would be a good > > value to start from? > GIN index build time is *very* sensitive to mainte

Re: [GENERAL] gin index creation performance problems

On Mon, 3 Nov 2008 16:45:35 +0100 Ivan Sergio Borgonovo <[EMAIL PROTECTED]> wrote: Forgot to add that top say postgresql is using 100% CPU and 15% memory. > I'm looking for a bit more guidance on gin index creation. > > The process: > - vaccum analyze. > - start a tran

[GENERAL] gin index creation performance problems

an 1h. maintenance_work_mem is still untouched. What would be a good value to start from? Anything else to do to improve performances? -- Ivan Sergio Borgonovo http://www.webthatworks.it -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription:

[GENERAL] raise notice, psql and having some feedback from scripts

I just have some batch work in scripts that I pass through: psql < script.sql &>script.log & or may be run by cron. In sql raise notice is not available. Is there any other way to send messages to the logs without polluting them too much with -a? -- Ivan Sergio

Re: [GENERAL] Are there plans to add data compression feature to postgresql?

than just TOAST. eg. if there are several columns that are frequently updated together... I'd say that compression could be one more tool for managing data integrity not that it will inevitably have a negative impact on it (nor a positive one if not correctly managed). What am I still missing? -- Ivan Sergio Borgonovo http://www.webthatworks.it -- 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] Are there plans to add data compression feature to postgresql?

a stored in a format that doesn't require a long queue of tools to be read. I do really hate dependencies that translates in hours of *boring* work if something turn bad. BTW I gave a glance to MonetDB papers posted earlier and it seems that compression algorithms are strongly r

Re: [GENERAL] tsearch2 problem

ere is some superposition with English. Till now it looks as an acceptable compromise but I wouldn't like to have surprises before I find the resources to actually do what should be done (fully support the 2 languages). -- Ivan Sergio Borgonovo http://www.webthatworks.it -- Sent via pg

Re: [GENERAL] overhead of plpgsql functions over simple select

e simple tests and it looks as being roughly 3 time faster. With higher numbers the difference seems to get smaller, maybe because of the higher cost of allocating memory caused by generate_series(?). So I know that immutable simple(?) functions are much faster in sql... anything else to avoid

[GENERAL] overhead of plpgsql functions over simple select

pared to a simple select? I'm not that worried of old query plans. thx -- Ivan Sergio Borgonovo http://www.webthatworks.it -- 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] Database design for separate tsearch table

ify that? I know the general differences between > gist and gin, but not how it affects weighted searches... http://www.postgresql.org/docs/8.3/static/textsearch-indexes.html search for @@@ -- Ivan Sergio Borgonovo http://www.webthatworks.it -- Ivan Sergio Borgonovo http://www.webthatworks.it -- 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] Database design for separate tsearch table

h magic would > require me to store the full name right there in the table, or am > I mistaken? You'll have to build up a tsvector for each language, so yeah it may be useful to store the tsvector together with the language with witch it was obtained. If you don't tsearch won't wor

[GENERAL] gin index and same query misteriously slowing down on a nearly-readonly DB

c. And then it goes no further. I've been able to vacuum full dropping the gin index and then vacuum and vacuum full... but it is still very very slow. -- Ivan Sergio Borgonovo http://www.webthatworks.it -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes

Re: [GENERAL] Shopping cart

QL and the changes you'll have to do to make it works may be minimal... but somehow core is more DB friendly than most modules so you may have some surprises. Still no popular Free cart I know rely on DB coherency features especially the ones written in PHP. -- Ivan Sergio Borgonovo http://www.

Re: [GENERAL] where column in ARRAY

On Wed, 22 Oct 2008 14:46:35 +0200 Ivan Sergio Borgonovo <[EMAIL PROTECTED]> wrote: > Without building up a dynamic query is it possible to: > > create or replace function t1(a int[]) as > $$ > ... > select * from t1 where c in a; // eg in spite of in (1,2,3); >

[GENERAL] where column in ARRAY

Without building up a dynamic query is it possible to: create or replace function t1(a int[]) as $$ ... select * from t1 where c in a; // eg in spite of in (1,2,3); or just obtain a similar effect? -- Ivan Sergio Borgonovo http://www.webthatworks.it -- Sent via pgsql-general mailing list

Re: [GENERAL] Shopping cart

ld be publicly published. The ones I'm aware of are Zen Cart, OSCommerce, Ubercart and Ecommerce (last 2 for drupal). I think they *may* work with postgresql as well. I think that anyway most of the popular prepackaged solutions don't support transactions in the DB. -- Ivan Sergio Borgono

[GENERAL] tsearch2: setting weights on tsquery plainto_tsquery with | or and weight

On Tue, 21 Oct 2008 13:40:33 -0400 Tom Lane <[EMAIL PROTECTED]> wrote: > Ivan Sergio Borgonovo <[EMAIL PROTECTED]> writes: > > I missed it. Thanks. Nearly perfect. Now I've to understand what > > a {} is. > > An array with a null element? an empty array

Re: exposing more parse was: Re: [GENERAL] tsearch2: setting weights on tsquery

On Tue, 21 Oct 2008 10:36:20 -0400 Tom Lane <[EMAIL PROTECTED]> wrote: > Ivan Sergio Borgonovo <[EMAIL PROTECTED]> writes: > > It would still be nice to be able to directly work with tsvector > > and tsquery so people could exploit the parser, lexer etc... and > &g

Re: exposing more parse was: Re: [GENERAL] tsearch2: setting weights on tsquery

On Tue, 21 Oct 2008 13:20:12 +0200 Ivan Sergio Borgonovo <[EMAIL PROTECTED]> wrote: > On Tue, 21 Oct 2008 10:29:52 +0200 > Ivan Sergio Borgonovo <[EMAIL PROTECTED]> wrote: > > I came across this: > http://grokbase.com/topic/2007/08/07/general-tsear

exposing more parse was: Re: [GENERAL] tsearch2: setting weights on tsquery

On Tue, 21 Oct 2008 10:29:52 +0200 Ivan Sergio Borgonovo <[EMAIL PROTECTED]> wrote: I came across this: http://grokbase.com/topic/2007/08/07/general-tsearch2-plainto-tsquery-with-or/r92nI5l_k9S4iKcWdCxKs05yFQk And I find it is strictly related to my needs. Working around ts_parse I could

[GENERAL] tsearch2: setting weights on tsquery

talog.english', coalesce(input1,'')), 'A') || ' ' || setweight(to_tsvector('pg_catalog.english', coalesce(input2,'')), 'B') ) and I won't be able rank on all fields at a time. -- Ivan Sergio Borgonovo http://www.webthatworks.it -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] parser: tokens in tsearch2

m number of characters to avoid _code get split in more than one lexeme. thanks -- Ivan Sergio Borgonovo http://www.webthatworks.it -- 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] Annoying Reply-To

e replies always shine here, but I couldn't resist to reply to people thinking I'm not "sensible", I took it personally ;) Evidently I'm old enough to know the existence of RFCs but not mature enough ;) -- Ivan Sergio Borgonovo http://www.webthatworks.it -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] searching through a catalogue with tsearch2 (examples, references, HOWTO)

3) how to flatten accents papà=papa etc... Other than the author's website and postgresql manual could someone point me to some good documentation, howto, examples? I'd appreciate even some general introduction to SE for catalogues that could be applied to tsearch2. thanks -- Ivan Serg

<    1   2   3   4   5   6   >