Re: [HACKERS] proposal: schema variables
Hello, Pavel. You wrote: PS> Hi, PS> I propose a new database object - a variable. The variable is PS> persistent object, that holds unshared session based not PS> transactional in memory value of any type. Like variables in any PS> other languages. The persistence is required for possibility to do PS> static checks, but can be limited to session - the variables can be temporal. Great idea. PS> My proposal is related to session variables from Sybase, MSSQL or PS> MySQL (based on prefix usage @ or @@), or package variables from PS> Oracle (access is controlled by scope), or schema variables from PS> DB2. Any design is coming from different sources, traditions and PS> has some advantages or disadvantages. The base of my proposal is PS> usage schema variables as session variables for stored procedures. PS> It should to help to people who try to port complex projects to PostgreSQL from other databases. PS> The Sybase (T-SQL) design is good for interactive work, but it PS> is weak for usage in stored procedures - the static check is not PS> possible. Is not possible to set some access rights on variables. PS> The ADA design (used on Oracle) based on scope is great, but our PS> environment is not nested. And we should to support other PL than PLpgSQL more strongly. PS> There is not too much other possibilities - the variable that PS> should be accessed from different PL, different procedures (in PS> time) should to live somewhere over PL, and there is the schema only. PS> The variable can be created by CREATE statement: PS> CREATE VARIABLE public.myvar AS integer; PS> CREATE VARIABLE myschema.myvar AS mytype; PS> CREATE [TEMP] VARIABLE [IF NOT EXISTS] name AS type PS> [ DEFAULT expression ] [[NOT] NULL] PS> [ ON TRANSACTION END { RESET | DROP } ] PS> [ { VOLATILE | STABLE } ]; PS> It is dropped by command DROP VARIABLE [ IF EXISTS] varname. PS> The access rights is controlled by usual access rights - by PS> commands GRANT/REVOKE. The possible rights are: READ, WRITE PS> The variables can be modified by SQL command SET (this is taken from standard, and it natural) PS> SET varname = expression; I propose LET keyword for this to distinguish GUC from variables, e.g. LET varname = expression; PS> Unfortunately we use the SET command for different purpose. But I PS> am thinking so we can solve it with few tricks. The first is PS> moving our GUC to pg_catalog schema. We can control the strictness PS> of SET command. In one variant, we can detect custom GUC and allow PS> it, in another we can disallow a custom GUC and allow only schema PS> variables. A new command LET can be alternative. PS> The variables should be used in queries implicitly (without JOIN) PS> SELECT varname; PS> The SEARCH_PATH is used, when varname is located. The variables PS> can be used everywhere where query parameters are allowed. PS> I hope so this proposal is good enough and simple. PS> Comments, notes? PS> regards PS> Pavel -- With best wishes, Pavel mailto:pa...@gf.microolap.com -- 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] GUC for cleanup indexes threshold.
Hello, Darafei. You wrote: DP> The following review has been posted through the commitfest application: DP> make installcheck-world: tested, passed DP> Implements feature: tested, passed DP> Spec compliant: tested, passed DP> Documentation:tested, passed DP> We're using Postgres with this patch for some time. DP> In our use case we've got a quickly growing large table with events from our users. DP> Table has a structure of (user_id, ts, ). Events are DP> append only, each user generates events in small predictable time frame, mostly each second. DP> From time to time we need to read this table in fashion of WHERE DP> ts BETWEEN a AND b AND user_id=c. DP> Such query leads to enormous amount of seeks, as records of each DP> user are scattered across relation and there are no pages that DP> contain two events from same user. DP> To fight it, we created a btree index on (user_id, ts, DP> ). Plan switched to index only scans, but heap fetches DP> and execution times were still the same. DP> Manual DP> We noticed that autovacuum skips scanning the relation and freezing the Visibility Map. DP> We started frequently performing VACUUM manually on the relation. DP> This helped with freezing the Visibility Map. DP> However, we found out that VACUUM makes a full scan over the index. DP> As index does not fit into memory, this means that each run DP> flushes all the disk caches and eats up Amazon IOPS credits. DP> With this patch behavior is much better for us - VACUUM finishes real quick. DP> As a future improvement, a similar improvement for other index types will be useful. DP> After it happens, I'm looking forward to autovacuum kicking in on DP> append-only tables, to freeze the Visibility Map. DP> The new status of this patch is: Ready for Committer Seems like, we may also going to hit it and it would be cool this vacuum issue solved for next PG version. -- With best wishes, Pavel mailto:pa...@gf.microolap.com -- 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] AlterUserStmt anmd RoleSpec rules in grammar.y
Hello, Robert. Sorry, if I was rough. My English is not so excellent. My point is that I was trying to distinguish behavior of EDB installer and "build from source" PG. And the result is that EDB executes ALTER USER and I don't know why. You wrote: RH> On Thu, Jul 27, 2017 at 2:52 AM, Pavel Golub wrote: >> One more notice. ALTER USER ALL works in EnterpriseDB 10beta2 >> installer. That's weird. I thought EnterpriseDB uses official sources. RH> I find it really hard to believe that we're doing anything else. It RH> wouldn't make any sense to patch the PostgreSQL source code and then RH> release the installers as PostgreSQL installers. And if we *were* RH> going to do that, wouldn't we patch something more interesting than RH> the ALTER USER command? I don't know what's going on here but I have RH> a feeling that EnterpriseDB secretly maintaining patch sets that we RH> inject into our PostgreSQL installers is not that thing. RH> Adding a few EDB people. -- With best wishes, Pavel mailto:pa...@gf.microolap.com -- 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] AlterUserStmt anmd RoleSpec rules in grammar.y
Hello, Tom. You wrote: TL> Pavel Golub writes: >> I need someone to throw some light on grammar (gram.y). >> I'm investigating beta2 regression tests, and found new statement >> `ALTER USER ALL SET application_name to 'SLAP';` >> ^^^ TL> You'll notice that that statement fails in the regression tests: TL> ALTER USER ALL SET application_name to 'SLAP'; TL> ERROR: syntax error at or near "ALL" One more notice. ALTER USER ALL works in EnterpriseDB 10beta2 installer. That's weird. I thought EnterpriseDB uses official sources. TL> The one that works is TL> ALTER ROLE ALL SET application_name to 'SLAP'; TL> and the reason is that AlterRoleSetStmt has a separate production TL> for ALL, but AlterUserSetStmt doesn't. This seems a tad bizarre TL> though. Peter, you added that production (in commit 9475db3a4); TL> is this difference intentional or just an oversight? If it's TL> intentional, what's the reasoning? TL> BTW, I'm quite confused as to why these test cases (in rolenames.sql) TL> seem to predate that commit, and yet it did not change their results. TL> regards, tom lane -- With best wishes, Pavel mailto:pa...@gf.microolap.com -- 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] AlterUserStmt anmd RoleSpec rules in grammar.y
Hello, Tom. You wrote: TL> Pavel Golub writes: >> I need someone to throw some light on grammar (gram.y). >> I'm investigating beta2 regression tests, and found new statement >> `ALTER USER ALL SET application_name to 'SLAP';` >> ^^^ TL> You'll notice that that statement fails in the regression tests: TL> ALTER USER ALL SET application_name to 'SLAP'; TL> ERROR: syntax error at or near "ALL" Oops! My bad! TL> The one that works is TL> ALTER ROLE ALL SET application_name to 'SLAP'; TL> and the reason is that AlterRoleSetStmt has a separate production TL> for ALL, but AlterUserSetStmt doesn't. Yeap, I see now separate rule for ALL in AlterRoleSetStmt. TL> This seems a tad bizarre TL> though. Peter, you added that production (in commit 9475db3a4); TL> is this difference intentional or just an oversight? If it's TL> intentional, what's the reasoning? TL> BTW, I'm quite confused as to why these test cases (in rolenames.sql) TL> seem to predate that commit, and yet it did not change their results. TL> regards, tom lane -- With best wishes, Pavel mailto:pa...@gf.microolap.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] AlterUserStmt anmd RoleSpec rules in grammar.y
Hello, hackers. I need someone to throw some light on grammar (gram.y). I'm investigating beta2 regression tests, and found new statement `ALTER USER ALL SET application_name to 'SLAP';` ^^^ I know for sure that in beta1 this operator fails. So I decided to recheck gram.y: AlterUserStmt: ALTER USER RoleSpec SetResetClause; RoleSpec:NonReservedWord | CURRENT_USER | SESSION_USER; But *ALL is reserved word*! Thus "ALTER ROLE\USER ALL" should fail. OK, I checked in Pg10 beta2, installer provided by EDB. It worked. Then I asked someone to check this against fresh built server from 'master'. It failed. So, the situation is: 1. Docs say this is correct statement: https://www.postgresql.org/docs/devel/static/sql-alterrole.html 2. The sources in master don't support such production: https://git.postgresql.org/gitweb/?p=postgresql.git;a=blob;f=src/backend/parser/gram.y;h=4b1ce09c445a5ee249a965ec0953b122df71eb6f;hb=refs/heads/master Line 1179 for AlterUserSetStmt rule; Line 14515 for RoleSpec rule; 3. EDB 10beta2 server supports it. What's going on? -- With best wishes, Pavel mailto:pa...@gf.microolap.com -- 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] Refactor pg_dump as a library?
Hello, Jakob. You wrote: JE> Would anybody else be interested in a pg_dump library? I've found JE> a thread from 2013 related to the idea, but the discussion came to nothing. JE> Thread started here: JE> http://www.postgresql.org/message-id/71e01949.2e16b.13df4707405.coremail.shuai900...@126.com JE> My Motivation: JE> I'm the developer of a PostgreSQL GUI client, and I am looking JE> for ways to integrate pg_dump into my application. The main use JE> case would be to get the dump of individual tables (for example, JE> when you want to create a table similar to an existing one) JE> Bundling pg_dump with my application and calling it doesn't allow JE> the fine grained control and integration I would like to have. JE> Also, pg_dump always opens a new connection; I would prefer to use JE> an existing database connection instead. JE> In case anybody else is interested in this, I can offer to JE> sponsor some developer time towards this effort. JE> Best regards, JE> Jakob I proposed this several times, but nobody cares. Then we did it. Our PostgresDAC component set (http://microolap.com/products/connectivity/postgresdac/) has TPSQLDump and TPSQLRestore classes. Details: http://microolap.com/products/connectivity/postgresdac/help/tpsqldump_tpsqldump.htm Also we've implemented PaGoDump and PaGoRestore utilities compatible with native pg_dump/pg_restore: http://microolap.com/products/database/pagodump/ -- With best wishes, Pavel mailto:pa...@gf.microolap.com -- 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] Database schema diff
Hello, Michal. Take a look in MicroOLAP Database Designer for PostgreSQL. You may use it in such way: 1. Reverse Engineering for existent database 2. Apply some changes 3. Modify database - you will get SQL script with all changes http://microolap.com/products/database/postgresql-designer/ You wrote: MN> Hi guys, MN> I would like to ask you whether is there any tool to be able to compare MN> database schemas ideally no matter what the column order is or to dump MN> database table with ascending order of all database columns. MN> For example, if I have table (called table) in schema A and in schema B MN> (the time difference between is 1 week) and I would like to verify the MN> column names/types matches but the order is different, i.e.: MN> Schema A (2015-10-01) | Schema B (2015-10-07) MN> | MN> id int| id int MN> name varchar(64) | name varchar(64) MN> text text | description text MN> description text | text text MN> Is there any tool to compare and (even in case above) return that both MN> tables match? Something like pgdiff or something? MN> This should work for all schemas, tables, functions, triggers and all MN> the schema components? MN> Also, is there any tool to accept 2 PgSQL dump files (source for MN> pg_restore) and compare the schemas of both in the way above? MN> Thanks a lot! MN> Michal -- With best wishes, Pavel mailto:pa...@gf.microolap.com -- 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] Remaining 'needs review' patchs in July commitfest
Hello, Heikki. You wrote: HL> 21 patches remain in Needs Review state, in the July commitfest. Some of HL> them have a reviewer signed up. I have highlighted some of them below HL> that worry me the most. What are we going to do about these? For each of HL> them, I'd like the authors to have some idea on what they need to do to HL> get the patch into committable state (or if the whole approach is going HL> to be rejected), but I don't know what that advise should be. >> COPY RAW HL> No consensus on whether to add this to the server's COPY command, or as HL> a new psql backslash option. I did quick review for this, however I need some more time for tests. May be I will do it next week. There is a consensus, only detailed review needed. HL> -- HL> - Heikki -- With best wishes, Pavel mailto:pa...@gf.microolap.com -- 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] raw output from copy
Hello Pavel. I looked through the patch. Sources are OK. However I didn't find any docs and test cases. Would you please provide me with short description on this feature and why it is important. Because I didn't manage to find the old Andrew Dunstan's post either. On Sat, Apr 11, 2015 at 12:26 AM, Pavel Stehule wrote: > Hi > > I wrote a prototype of this patch, and it works well > > postgres=# set client_encoding to 'latin2'; > SET > Time: 1.488 ms > postgres=# \copy (select xmlelement(name xx, d) from d) to ~/d.xml (format > 'raw') > COPY 1 > Time: 1.108 ms > postgres=# copy (select xmlelement(name xx, d) from d) to stdout (format > 'raw') ; > příliš žluťoučký kůňTime: > 1.000 ms > > Regards > > Pavel > > 2015-04-09 20:48 GMT+02:00 Pavel Stehule : > >> Hi >> >> This thread was finished without real work. I have a real use case - >> export XML doc in non utf8 encoding. >> >> http://www.postgresql.org/message-id/16174.1319228...@sss.pgh.pa.us >> >> I propose to implement new format option "RAW" like Tom proposed. >> >> It requires only one row, one column result - and result is just raw >> binary data without size. >> >> Objections? Ideas? >> >> Regards >> >> Pavel >> > > > > -- > Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-hackers > > -- Nullus est in vitae sensus ipsa vera est sensus.
Re: [HACKERS] 9.5: UPDATE/DELETE .. ORDER BY .. LIMIT ..
Hello, Robert. You wrote: RH> On Sat, Feb 22, 2014 at 7:02 PM, Rukh Meski wrote: >> Sorry, I wanted to minimize the attention my message attracts. I mostly >> posted it to let people know I plan on working on this for 9.5 to avoid >> duplicated effort. I will post more documentation and my reasons for >> wanting this feature in postgre later, if that's all right. RH> I've wanted this more than once. I suspect it's a pretty hard project, though. +1 from me. This is the exciting functionality. There was even a poll in my blog year ago: http://pgolub.wordpress.com/2012/11/23/do-we-need-limit-clause-in-update-and-delete-statements-for-postgresql/ So the results were (for those who don't want check the post): Yes, for functionality: 194 (61.4%) No way! 78 (24.7%) Do not care 44 (13.9%) RH> -- RH> Robert Haas RH> EnterpriseDB: http://www.enterprisedb.com RH> The Enterprise PostgreSQL Company -- With best wishes, Pavel mailto:pa...@gf.microolap.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] getenv used in libpq caused missing values under Windows
Hello, Pgsql-hackers. As you probably know dealing with Windows MSVCRT is some kind of hell. That's why we have src/port/win32env.c particulalry, because there may be several CRT's loaded at the same time. libpq unfortunately is using standard "getenv" function call to fill connection parameters inside "conninfo_add_defaults()", see http://www.postgresql.org/docs/9.3/interactive/libpq-envars.html This happens because MSVCRT has it's own local copy of environment variables table, and this table is not updated during execution. So if one used "SetEnvironmentVariable()" Windows API to set any variable, e.g. PGHOST, then this change will not be visible inside libpq's "conninfo_add_defaults()" function where all default values are obtained. This situation especially unpleasant for non-C developers, since there is no opportunity to use standard "putenv" function or ported "pgwin32_putenv". My proposal is to implement "pgwin32_getenv" function which will call GetEnvironmentVariable first and if it's NULL then call MSVCRT's "getenv" in the same way as "pgwin32_putenv" does. So now the bad scenario is: 1. SetEnvironmentVariable('PGHOST=192.188.9.9') 2. PQconnectdbParams without specifying host parameter will fail -- With best wishes, Pavel mailto:pa...@gf.microolap.com -- 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] Proposal: variant of regclass
Hello, Andres. You wrote: AF> On 2013-12-04 20:25:53 -0500, Tom Lane wrote: >> Tatsuo Ishii writes: >> > I would like to add a variant of regclass, which is exactly same as >> > current regclass except it does not raise an error when the target >> > table is not found. Instead it returns InvalidOid (0). >> >> I've sometimes thought we should just make all the reg* input converters >> act that way. It's not terribly consistent that they'll happily take >> numeric inputs that don't correspond to any existing OID. And more >> often than not, I've found the throw-an-error behavior to be annoying >> not helpful. AF> I find that to be a bit of a scary change. I have seen application check AF> for the existance of tables using the error thrown by ::regclass. Now, AF> they could change that to check for IS NULL which would be better for AF> them performancewise, but the likelihood they will notice in time seems AF> small. I personally see two approaches: 1. Implement GUC variable controling this behaviour per session 2. Introduce new safe reg* variables, e.g. "sregclass", "sregtype" etc. AF> Greetings, AF> Andres Freund AF> -- AF> Andres Freund http://www.2ndQuadrant.com/ AF> PostgreSQL Development, 24x7 Support, Training & Services -- With best wishes, Pavel mailto:pa...@gf.microolap.com -- 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] Proposal: variant of regclass
Hello, Tom. You wrote: TL> Tatsuo Ishii writes: >> I would like to add a variant of regclass, which is exactly same as >> current regclass except it does not raise an error when the target >> table is not found. Instead it returns InvalidOid (0). TL> I've sometimes thought we should just make all the reg* input converters TL> act that way. Absolutely agree. I cannot see the case whn error is the appropriate solution. Casting nonexistent objects to NULL is the way to go for me. TL> It's not terribly consistent that they'll happily take TL> numeric inputs that don't correspond to any existing OID. And more TL> often than not, I've found the throw-an-error behavior to be annoying TL> not helpful. TL> In any case, -1 for dealing with this only for regclass and not the TL> other ones. TL> regards, tom lane -- With best wishes, Pavel mailto:pa...@gf.microolap.com -- 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] commit fest 2013-11 week 1 report
Hello, Peter. Is is possible to add small patch to the current commit fest? You wrote: PE> We started with PE> Fri Nov 15 PE> Status Summary. Needs Review: 79, Waiting on Author: 7, Ready for PE> Committer: 5, Committed: 7, Returned with Feedback: 3, Rejected: 1. Total: 102. PE> We are now at PE> Fri Nov 22 PE> Status Summary. Needs Review: 47, Waiting on Author: 28, Ready PE> for Committer: 10, Committed: 18, Returned with Feedback: 3, Rejected: 3. Total: 109. PE> (some late arrivals, some patches split) PE> Progress has been quite good. PE> Almost all patch authors responded to my call to sign up for reviewing PE> someone else's patch. PE> 20 patches are still without reviewer. Most of those are the typical PE> difficult topics (indexes, replication), so now might be a good time PE> for experts in those areas to start picking up the remaining patches. PE> In the coming week, we will be following up with reviewers to send in PE> their first review if they haven't already done so. -- With best wishes, Pavel mailto:pa...@gf.microolap.com -- 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] LISTEN / NOTIFY enhancement request for Postgresql
Hello, Sev. You wrote: SZ> Thank you all for considering my feature request. SZ> Dimitri's suggestion is a very good one - I feel it will accomplish the SZ> goal of allowing more granularity in the "Listen". SZ> We might also want to add a flag in postgresql.conf to disable this SZ> enhancement so that we don't break existing code. I suppose it should be GUC variable (not only global entry) for per session settings. SZ> On 11/15/2013 8:19 AM, Pavel Golub wrote: >> Hello, Dimitri. >> >> You wrote: >> >> DF> Bruce Momjian writes: >>>>>• is used to separate names in a path >>>>>• * is used to match any name in a path >>>>>• > is used to recursively match any destination starting from this >>>>> name >>>>> >>>>> For example using the example above, these subscriptions are possible >>>>> >>>>> Subscription Meaning >>>>> PRICE.> Any price for any product on any exchange >>>>> PRICE.STOCK.>Any price for a stock on any exchange >>>>> PRICE.STOCK.NASDAQ.* Any stock price on NASDAQ >>>>> PRICE.STOCK.*.IBMAny IBM stock price on any exchange >>>>> >>>>> >>>>> My request is to implement the same or similar feature in Postgresql. >>>> This does seem useful and pretty easy to implement. Should we add a >>>> TODO? >> DF> I think we should consider the ltree syntax in that case, as documented >> DF> in the following link: >> >> DF> http://www.postgresql.org/docs/9.3/interactive/ltree.html >> >> Great idea! Thanks for link. >> >> DF> Regards, >> DF> -- >> DF> Dimitri Fontaine >> DF> http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support >> >> >> >> >> -- With best wishes, Pavel mailto:pa...@gf.microolap.com -- 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] LISTEN / NOTIFY enhancement request for Postgresql
Hello, Dimitri. You wrote: DF> Bruce Momjian writes: >>> • is used to separate names in a path >>> • * is used to match any name in a path >>> • > is used to recursively match any destination starting from this name >>> >>> For example using the example above, these subscriptions are possible >>> >>> Subscription Meaning >>> PRICE.> Any price for any product on any exchange >>> PRICE.STOCK.>Any price for a stock on any exchange >>> PRICE.STOCK.NASDAQ.* Any stock price on NASDAQ >>> PRICE.STOCK.*.IBMAny IBM stock price on any exchange >>> >>> >>> My request is to implement the same or similar feature in Postgresql. >> >> This does seem useful and pretty easy to implement. Should we add a >> TODO? DF> I think we should consider the ltree syntax in that case, as documented DF> in the following link: DF> http://www.postgresql.org/docs/9.3/interactive/ltree.html Great idea! Thanks for link. DF> Regards, DF> -- DF> Dimitri Fontaine DF> http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support -- With best wishes, Pavel mailto:pa...@gf.microolap.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] exit_horribly vs exit_nicely in pg_dump
Hello. Examining pg_dump sources recently I've found that different exit procedure used for the same situations. A quick example from pg_dump.c: if (dataOnly && schemaOnly) exit_horribly(NULL, "options -s/--schema-only and -a/--data-only cannot be used together\n"); if (dataOnly && outputClean) exit_horribly(NULL, "options -c/--clean and -a/--data-only cannot be used together\n"); if (dump_inserts && oids) { write_msg(NULL, "options --inserts/--column-inserts and -o/--oids cannot be used together\n"); write_msg(NULL, "(The INSERT command cannot set OIDs.)\n"); exit_nicely(1); } I suppose this should be call to exit_nicely() for all possible cases. The only need for calling exit_horribly() is when we are deep down in the multithreaded code, AFAIK. -- With best wishes, Pavel mailto:pa...@gf.microolap.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Error message for CREATE VIEW is confusing
Hello, PostgreSQL. Let's assume we have created MATERIALIZED VIEW, e.g. CREATE MATERIALIZED VIEW customer_v AS SELECT ; Then one wants to redefine this view as a regular view, e.g. CREATE OR REPLACE VIEW customer_v AS ; Error is rising: ERROR: "customer_v" is not a view ** Error ** ERROR: "customer_v" is not a view SQL-state: 42809 Should we change error message to something like "customer_v" has wrong object type" (according to errcode appendix)? Or should we change word "view" to "regular view" since we have "materialized" already, e.g. "customer_v" is not a regular view"? -- With best wishes, Pavel mailto:pa...@gf.microolap.com -- 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] Slicing TOAST
Hello, Heikki. You wrote: HL> On 14.05.2013 21:36, Josh Berkus wrote: >> >>> I'm proposing this now as a possible GSoC project; I don't propose to >>> actively work on it myself. >> >> The deadline for submitting GSOC projects (by students) was a week ago. >> So is this a project suggestion for next year ...? HL> I've been thinking, we should already start collecting ideas for next HL> year, and collect them throughout the year. I know I come up with some HL> ideas every now and then, but when it's time for another GSoC, I can't HL> remember any of them. HL> I just created a GSoC2014 ideas pages on the wiki, for collecting these: HL> https://wiki.postgresql.org/wiki/GSoC_2014. Let's keep the ideas coming, HL> throughout the year. Good idea! It reminds about feature proposed by Pavel Stehule while ago here: http://www.postgresql.org/message-id/banlktini+chgkfnyjkf1rshsq2kmkts...@mail.gmail.com It's about streaming functionality for BYTEA type. But I think streaming must be added to BYTEA, TEXT and VARCHAR without length specifier too. As Pavel stated: "A very large bytea are limited by query size - processing long query needs too RAM". This is the holy true, which came up suddenly in the project of one of my client. Becuase he used bytea for images storing and text format in PQexec, which as you know doubles-triples size of the data. Some more details from Pavel: There is a few disadvantages LO against bytea, so there are requests for "smarter" API for bytea. Significant problem is different implementation of LO for people who have to port application to PostgreSQL from Oracle, DB2. There are some JDBC issues too. For me - main disadvantage of LO in one space for all. Bytea removes this disadvantage, but it is slower for lengths > 20 MB. It could be really very practical have a possibility insert some large fields in second NON SQL stream. Same situation is when large bytea is read. I'm not sure if the whole project is simple enough for GSOC, but I suppose it may be splitted somehow. PS Should we start separate thread for proposals, because I've spent an hour since I found wiki for GSOC14 mention. HL> - Heikki -- With best wishes, Pavel mailto:pa...@gf.microolap.com -- 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] [GSOC] questions about idea "rewrite pg_dump as library"
Hello, Tom. You wrote: TL> Pavel Golub writes: >> From my point of view the new library should export only two >> functions: >> 1. The execution function: >> ExecStatusType PGdumpdbParams(const char * const *keywords, >> const char * const *values); TL> No, this is exactly *wrong*. You might as well not bother to refactor, TL> if the only API the library presents is exactly equivalent to what you TL> could get with system("pg_dump ..."). Well, yes. You're absolutely right. But should this be a starting point? TL> I don't know what the right answer is, but this isn't it. Most people TL> who are interested in this topic are interested because they want to get TL> output that is different from anything pg_dump would produce on its own, TL> for instance applying a more complex object-selection rule than anything TL> pg_dump offers. Right now, the only way they can do that is lobby to TL> add new switch options to pg_dump. With a change like this, it'd still TL> be the case that they can't get what they want except by adding new TL> switch options to pg_dump. I don't see any advantage gained. TL> regards, tom lane -- With best wishes, Pavel mailto:pa...@gf.microolap.com -- 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] [GSOC] questions about idea "rewrite pg_dump as library"
Hello, 帅. You wrote: 帅> Hi all, 帅> I'd like to introduce myself to the dev community. I am Shuai 帅> Fan, a student from Dalian University of Technology, DLUT , for 帅> short, China. And I am interested in working with PostgreSQL project in GSOC2013. 帅> I'm interested in the idea "Rewrite (add) pg_dump and 帅> pg_restore utilities as libraries (.so, .dll & .dylib)". 帅> These days, I had a talk with Mr. Pavel Golub by email, the 帅> author of this post. And asked some questions about this idea. He 帅> adviced me to post the question to this mail list. 帅> My question is: 帅> There are lots of functions in "pg_dump.c". If I rewrite this 帅> file as library. I should split "pg_dump.c" into two or more 帅> files(Mr. Pavel Golub's advice). However, some functions, do have return value. e.g. 帅> static DumpableObject *createBoundaryObjects(void); 帅> I thought, these functions must get return value through 帅> function argument by passing pointer to it, when using library. 帅> But, the question is: If I did so, function prototype may be 帅> changed. And Mr. Pavel Golub said "it's better to keep all 帅> functions with the same declaration", and so "we will only have one 帅> code base for both console application and library". I think he is right. 帅> But then, how can I deal with this situation? From my point of 帅> view, I can't malloc a variable (e.g. DumpableObject) in one 帅> library (e.g. pg_dumplib.so), and then return it's pointer to 帅> another library (or excutable program). Maybe, it's not safe(?). Or 帅> has something to do with "memory leak"(?). I'm not sure. 帅> 帅> Do you have any suggestions? From my point of view the new library should export only two functions: 1. The execution function: ExecStatusType PGdumpdbParams(const char * const *keywords, const char * const *values); Return type may be other, but ExecStatusType seems to be OK for this purpose: PGRES_TUPLES_OK - for success, PGRES_FATAL_ERROR - for fail. Parameters will remain the same as usual command-line options for pg_dump. Thus we will have less work for existing application, e.g. pgAdmin. 2. Logging or notice processing function: typedef void (*PGdumplogProcessor) (char *relname, int done, char *message); PGdumplogProcessor PQsetNoticeProcessor(PGdumplogProcessor proc, void *arg); The purpose of this function is process output of the dump. The first argument is callback-function which accepts information about current relname (or operation, or stage), done indicates how much work done (for progress bars etc.), message contains some extra information. That's all! Only two functions. Indeed we don't need all those low-level dump functions like createBoundaryObjects etc. There will be the only one entry to the whole logic. And if one wants the only one table, funcction or schema - combination of correct parameters should be passed to PGdumpdbParams. 帅> Best wishes, 帅> Shuai -- With best wishes, Pavel mailto:pa...@gf.microolap.com -- 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] Building on MinGW
Hello, Jeff. You wrote: JJ> Changed subject from "Strange Windows problem, lock_timeout test request" JJ> On Thu, Jan 24, 2013 at 11:41 AM, Andrew Dunstan wrote: JJ> On 01/24/2013 01:44 PM, Jeff Janes wrote: JJ> On Sat, Jan 19, 2013 at 12:15 PM, Andrew Dunstan wrote: JJ> On 01/19/2013 02:36 AM, Boszormenyi Zoltan wrote: JJ> A long time ago I had a lot of sympathy with this answer, but these days not JJ> so much. Getting a working mingw/msys environment sufficient to build a bare JJ> bones PostgreSQL from scratch is both cheap and fairly easy. The JJ> improvements that mingw has made in its install process, and the presence of JJ> cheap or free windows instances in the cloud combine to make this pretty JJ> simple. But since it's still slightly involved here is how I constructed JJ> one such this morning: JJ> I've used this description, skipping the Amazon part and putting it JJ> directly on my Windows computer, and it worked. JJ> Except bin/pg_ctl does not work. It just silently exits without doing JJ> anything, so I have to use bin/postgres to start the database (which JJ> is what "make check" uses anyway, so not a problem if you just want JJ> make check). Is that just me or is that a known problem? I've seen JJ> some discussion from 2004, but didn't find a conclusion. JJ> Did you copy libpq.dll from the lib directory to the bin JJ> directory? If not, try that and see if it fixes the problem. JJ> I've now done that, and it did fix the problem. I can start the JJ> database with pg_ctl.exe if I want. JJ> Should the makefile do this for us? Or is there a way to JJ> configure so that is not needed (whatever the MinGW equivalent is to an rpath?) JJ> psql.exe now runs, but it seems to be broken. It hangs forever JJ> on attempting to connect to any server (either the local one JJ> compiled with MinGW, or a remote server running on Linux). JJ> psql on the remote linux machine can connect back to the Windows JJ> server compiled with MinGW, so the problem seems to be with JJ> MinGW's psql.exe, not its server. JJ> Doesn't "make check" have to use something which is morally JJ> equivalent to psql.exe? If so, how can it pass if psql.exe is broken? JJ> I've put up a wiki page: JJ> https://wiki.postgresql.org/wiki/Building_With_MinGW Jeff, I think inromation about installing dependencies should be added to this Wiki. At least zlib and openssl are "must have" for libpq, psql and pg_dump\pg_restore JJ> Cheers, JJ> Jeff -- With best wishes, Pavel mailto:pa...@gf.microolap.com -- 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] Call for Google Summer of Code mentors, admins
Hello, Josh. You wrote: JB> Folks, JB> Once again, Google is holding Summer of Code. We need to assess whether JB> we want to participate this year. JB> Questions: JB> - Who wants to mentor for GSOC? JB> - Who can admin for GSOC? Thom? JB> - Please suggest project ideas for GSOC My suggestion is to rewrite (add) pg_dump and pg_restore utilities as libraries (.so, .dll & .dylib). For me as a developer it will be a cool feature. And I can be a mentor for this project of course! JB> - Students seeing this -- please speak up if you have projects you plan JB> to submit. JB> -- JB> Josh Berkus JB> PostgreSQL Experts Inc. JB> http://pgexperts.com -- With best wishes, Pavel mailto:pa...@gf.microolap.com -- 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] proposal: regrole type?
Hello, Pavel. You wrote: PS> Hello PS> Can we implement REGROLE type, that simplify role name <-> oid transformations? +1 from me. My old wish. PS> Regards PS> Pavel -- With best wishes, Pavel mailto:pa...@gf.microolap.com -- 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] pg_tablespace.spclocation column removed in 9.2
Hello, Guillaume. You wrote: GL> Hi Pavel, GL> On Mon, 2012-06-25 at 08:26 +0300, Pavel Golub wrote: >> Hello, Pgsql-bugs. >> >> According to the "Moving tablespaces" thread started by Bruce >> http://archives.postgresql.org/pgsql-docs/2011-12/msg3.php >> pg_tablespace.spclocation column is removed in the 9.2beta. However >> this breaks backward compatibility for a bunch of products, e.g. >> pgAdmin, phpPgAdmin, PgMDD etc. >> >> I'm not sure this is the best choice. Because each application with >> tablespace support will need additional check now to determine what >> way to use for obtaining tablespace location: >> pg_get_tablespace_location(oid) or tablespace.spclocation >> >> I'm aware of problems caused by this hard coded column. My proposal is >> to convert pg_tablespace to system view may be? >> GL> I don't see why it causes you so much trouble. Not so much. However. GL> You should already have GL> many locations in your code where you need to check the version to be GL> compatible with the latest major releases. This is holy true. GL> I know pgAdmin does. So I GL> guess that one more is not a big deal. GL> And this change in PostgreSQL helps a lot DBAs who want to move GL> tablespaces (not really common work AFAIK, I agree). I know. I just followed the advice of Josh Berkus and added this as a bug. -- With best wishes, Pavel mailto:pa...@gf.microolap.com -- 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_tablespace.spclocation column removed in 9.2
Hello, Pgsql-bugs. According to the "Moving tablespaces" thread started by Bruce http://archives.postgresql.org/pgsql-docs/2011-12/msg3.php pg_tablespace.spclocation column is removed in the 9.2beta. However this breaks backward compatibility for a bunch of products, e.g. pgAdmin, phpPgAdmin, PgMDD etc. I'm not sure this is the best choice. Because each application with tablespace support will need additional check now to determine what way to use for obtaining tablespace location: pg_get_tablespace_location(oid) or tablespace.spclocation I'm aware of problems caused by this hard coded column. My proposal is to convert pg_tablespace to system view may be? -- With best wishes, Pavel mailto:pa...@gf.microolap.com -- 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] Google Summer of Code? Call for mentors.
Hello, Josh. You wrote: JB> Hackers, JB> The call is now open for Google Summer of Code. JB> If you are interested in being a GSoC mentor this summer, please reply JB> to this email. I want to gauge whether or not we should participate JB> this summer. JB> -- JB> Josh Berkus JB> PostgreSQL Experts Inc. JB> http://pgexperts.com What are the requirements for mentors? -- With best wishes, Pavel mailto:pa...@gf.microolap.com -- 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] Can PQstatus() be used by Application to check connection to postgres periodically?
Hello, sujayr06. You wrote: s> Hello All, s>My application has to do a real time data upload to PostgreSQL s> server. s>Every time i have to do a real time upload, i do not wish to open s> new connection. s>I want to open a connection once [when my application comes up] s> and periodically check if the connection is active. s>Can PQstatus() be used by application to check the status of the s> connection already established? s>If PQstatus() cannot be used, does PostgreSQL provide alternate s> interface to check the status of the connection. s> Note : I do not wish to open connection on every real time upload s> as its an overhead for the application. s>Appreciate any help! You may use PQtransactionStatus for this purpose (http://www.postgresql.org/docs/9.1/static/libpq-status.html) s> WBR, s> Sujay s> s> s> -- s> View this message in context: s> http://postgresql.1045698.n5.nabble.com/Can-PQstatus-be-used-by-Application-to-check-connection-to-postgres-periodically-tp5462315p5462315.html s> Sent from the PostgreSQL - hackers mailing list archive at Nabble.com. -- With best wishes, Pavel mailto:pa...@gf.microolap.com -- 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] LibreOffice driver 1: Building libpq with Mozilla LDAP instead of OpenLDAP
Hello, Tom. You wrote: TL> Greg Smith writes: >> On 12/13/2011 11:07 AM, Lionel Elie Mamane wrote: >>> On MacOS X and Microsoft Windows, the world is far more messy. There >>> are several ways to install libpq (one-click installer, fink, >>> MacPorts, ...), and each of these ways allows the user to select an >>> install location (or a prefix thereof) freely. >> This is less controversial. TL> Yeah, I'm aware that sane handling of library dependencies is TL> practically impossible under Windows, but I didn't see how that would TL> result in wanting to change the configure script. I wasn't thinking TL> about OSX though. (You're aware that Apple ships a perfectly fine TL> libpq.so in Lion, no?) Is it true? Really? Where can we read about it? TL> regards, tom lane -- With best wishes, Pavel mailto:pa...@gf.microolap.com -- 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] [PATCH] PostgreSQL fails to build with 32bit MinGW-w64
Hello, Lars. You wrote: LK> Hi PostgreSQL hackers, LK> LK> support for Mingw-w64 compiler was added to postgres with commit LK> 91812df. Unfortunately only the 64 bit output is working right LK> now. This issue was already highlighted with initial patch in LK> http://archives.postgresql.org/pgsql-bugs/2011-07/msg00059.php LK> LK> Mingw-w64 uses the same header files for 32 and 64 bit compiles. LK> So the same conditions apply to mingw-w32 bit as for the WIN64 LK> case. In WIN64 "WSAAPI" is defined to nothing, but in 32 bit to LK> stdcall, so it needs to be used in the accept-parameter check, LK> too. Maybe you prefer PASCAL instead of WSAAPI in configure. LK> LK> I tested successful compilation for the following platforms: LK> - i686-w64-mingw32 - gcc v4.6.1 LK> - x86_64-w64-mingw32 - gcc v4.6.1 LK> - i586-mingw32msvc - gcc v4.4.4 LK> - x86_64-linux-gnu - gcc v4.6.1 LK> Can you please provide me with some howto on building PG sources with mingw-w64? -- With best wishes, Pavel mailto:pa...@gf.microolap.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Client library cross-compiling: Win32, Win64, MacOSX. Possible?
Hello. Are there any howto's or articles about building client access library (libpq) for several target OSes, e.g. Win32, Win64, MacOS in the same MinGW environment? And is it possible at all? I know that there is MinGW-w64 to produce Win64 binaries, but I want to have one farm for all. If not, is there any opportunity to have needed binaries from some postgresql build farms? -- With best wishes, Pavel mailto:pa...@gf.microolap.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] MicrOLAP Database Designer with PostgreSQL 9.1 support is out!
Hello. Database Designer for PostgreSQL is an easy CASE tool which works natively under Windows OS family and Linux under Wine/WineHQ. This release introduces new functionality as well as several bug fixes. Support for PostgreSQL 9.1 added, new Create HTML Report functionality present, unlogged tables support added, Database Generation and Modification dialogs improved. You're welcome to download the Database Designer for PostgreSQL 1.8.1 right now at: http://microolap.com/products/database/postgresql-designer/download/ Full changelog: [+] Name patterns for newly created objects supported [+] Object selection added to Create HTML Report dialog [+] Support for unlogged tables using the UNLOGGED option added [*] Additional checks added for dependent composite types during model loading [*] Improved processing for fields deletion with referenced foreign keys in Modify engine [*] Syntax highlightning improved for base types [-] "Access violation on Privilege Manager Add button click" bug fixed [-] "Cannot cast type text[] to text error duting Reverse Engineering on pre-8.2 servers" bug fixed [-] "EInvalidCast error occurs during deletion of domain in Domain & UDT Manager" bug fixed [-] "Relation 'pg_users' does not exist during Reverse Engineering on pre-8.1 servers" bug fixed [-] "Some sequences may not be generated using Generate Database dialog" bug fixed [-] "Stamp colors are not saved" bug fixed [-] "Stored Routine Editor dissalows non-latin character in routine name" bug fixed Please don't hesitate to ask any questions or report bugs with our Support Ticketing system available at http://www.microolap.com/support/ -- With best wishes, Pavel mailto:pa...@gf.microolap.com -- 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] pg_dump.c
Hello, Andrew. You wrote: AD> In the "refactoring Large C files" discussion one of the biggest files AD> Bruce mentioned is pg_dump.c. There has been discussion in the past of AD> turning lots of the knowledge currently embedded in this file into a AD> library, which would make it available to other clients (e.g. psql). +1 It would be great to have library with such functionality! AD> I'm AD> not sure what a reasonable API for that would look like, though. Does AD> anyone have any ideas? AD> cheers AD> andrew -- With best wishes, Pavel mailto:pa...@gf.microolap.com -- 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] rc1 or beta4?
Hello, Dave. You wrote: DP> The current plan (or, the last one I recall) is to push another 9.1 DP> release tomorrow, for Monday release. Are we going with beta4 or rc1? +1 for RC1 DP> -- DP> Dave Page DP> Blog: http://pgsnake.blogspot.com DP> Twitter: @pgsnake DP> EnterpriseDB UK: http://www.enterprisedb.com DP> The Enterprise PostgreSQL Company -- With best wishes, Pavel mailto:pa...@gf.microolap.com -- 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] fixing PQsetvalue()
Hello, Merlin. I hope it's OK that I've added Andrew's patch to CommitFest: https://commitfest.postgresql.org/action/patch_view?id=606 I did this becuase beta3 already released, but nut nothig is done on this bug. You wrote: MM> On Thu, Jun 23, 2011 at 7:54 AM, Andrew Chernow wrote: >>> you are creating as you iterate through. This behavior was >>> unnecessary in terms of what libpqtypes and friends needed and may (as >>> Tom suggested) come back to bite us at some point. As it turns out, >>> PQsetvalue's operation on results that weren't created via >>> PQmakeEmptyResult was totally busted because of the bug, so we have a >>> unique opportunity to tinker with libpq here: you could argue that you >>> >>> +1 >>> >>> Exactly at this moment I am thinking about using modifiable >>> (via PQsetvalue) PGresult instead of std::map in my C++ library >>> for store parameters for binding to executing command. >>> I am already designed how to implement it, and I supposed that >>> PQsetvalue is intended to work with any PGresult and not only >>> with those which has been created via PQmakeEmptyResult... >>> So, I am absolutely sure, that PQsetvalue should works with >>> any PGresult. >> >> All PGresults are created via PQmakeEmptyPGresult, including libpqtypes. >> Actually, libpqtypes calls PQcopyResult which calls PQmakeEmptyPGresult. >> >> It might be better to say a "server" result vs. a "client" result. >> Currently, PQsetvalue is broken when provided a "server" generated result. MM> er, right-- the divergence was in how the tuples were getting added -- MM> thanks for the clarification. essentially your attached patch fixes MM> that divergence. MM> merlin -- With best wishes, Pavel mailto:pa...@gf.microolap.com -- 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] Error in PQsetvalue
Hello, Andrew. I hope you don't mind I've added this patch to CommitFest: https://commitfest.postgresql.org/action/patch_view?id=606 You wrote: AC> On 6/3/2011 10:26 PM, Andrew Chernow wrote: >> I disagree -- I think the fix is a one-liner. line 446: if (tup_num == res->ntups&& !res->tuples[tup_num]) should just become if (tup_num == res->ntups) also the memset of the tuple slots when the slot array is expanded can be removed. (in addition, the array tuple array expansion should really be abstracted, but that isn't strictly necessary here). >>> >>> All true. This is a cleaner fix to something that was in fact broken ;) You >>> want >> >> Attached a patch that fixes the OP's issue. PQsetvalue now uses pqAddTuple to >> grow the tuple table and has removed the remnants of an older idea that >> caused >> the bug. >> AC> Sorry, I attached the wrong patch. Here is the correct one. -- With best wishes, Pavel mailto:pa...@gf.microolap.com -- 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] fixing PQsetvalue()
Hello. Any news on these issues? Becuase beta3 is scheduled for July 11th... You wrote: MM> On Jun 6 MM> (http://archives.postgresql.org/pgsql-hackers/2011-06/msg00272.php), MM> Pavel discovered an issue with PQsetvalue that could cause libpq to MM> wander off into unallocated memory that was present in 9.0.x. A MM> fairly uninteresting fix was quickly produced, but Tom indicated MM> during subsequent review that he was not happy with the behavior of MM> the function. Everyone was busy with the beta wrap at the time so I MM> didn't press the issue. MM> A little history here: PQsetvalue's MM> (http://www.postgresql.org/docs/9.0/static/libpq-misc.html) main MM> reason to exist is to allow creating a PGresult out of scratch data on MM> a result created via PQmakeEmptyResult(). This behavior works as MM> intended and is not controversial...it was initially done to support MM> libpqtypes but has apparently found use in other places like ecpg. MM> PQsetvalue *also* allows you to mess with results returned by the MM> server using standard query methods for any tuple, not just the one MM> you are creating as you iterate through. This behavior was MM> unnecessary in terms of what libpqtypes and friends needed and may (as MM> Tom suggested) come back to bite us at some point. As it turns out, MM> PQsetvalue's operation on results that weren't created via MM> PQmakeEmptyResult was totally busted because of the bug, so we have a MM> unique opportunity to tinker with libpq here: you could argue that you MM> have a window of opportunity to change the behavior here since we know MM> it isn't being usefully used. I think it's too late for that but it's MM> if it had to be done the time is now. MM> Pavel actually has been requesting to go further with being able to MM> mess around with PGresults (see: MM> http://archives.postgresql.org/pgsql-interfaces/2011-06/msg0.php), MM> such that the result would start to have some 'recordset' features you MM> find in various other libraries. Maybe it's not the job of libpq to MM> do that, but I happen to think it's pretty cool. Anyways, something MM> has to be done -- I hate to see an unpatched known 9.0 issue remain in MM> the wild. MM> merlin -- With best wishes, Pavel mailto:pa...@gf.microolap.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [BUGS] [HACKERS] COPY .... WITH (FORMAT binary) causes syntax error at or near "binary"
Hello, Robert. You wrote: RH> On Tue, Jul 5, 2011 at 11:37 AM, Pavel Golub wrote: >> RH> Yeah. In particular, it conflicts with the ancient copy syntax which >> RH> we still support for backwards compatibility with versions < 7.3. We >> RH> can fix the immediate problem with something like the attached. >> >> This patch is ugly. Sorry, Robert, but it's true. RH> No hard feelings here. If you, as the reporter of the problem, don't RH> feel that it's serious enough to warrant back-patching a fix, then I'm RH> not going to insist. However, if we don't do what I've proposed here, RH> then I think 8.4 and 9.0 and probably 9.1 are going to need to stay as RH> they are, because... >> RH> (c) Should we consider removing compatibility with the ancient copy >> RH> syntax in 9.2, and de-reserving that keyword? (Given that the >> RH> workaround is this simple, I'm inclined to say "no", but could be >> RH> persuaded otherwise.) >> >> +1 for this. Pre-7.3 syntax is dead in fact for many years. RH> ...this is not something we're going to back-patch. Patches needed for 9.0 and 9.1 only, because this is new format comparing with 8.x -- With best wishes, Pavel mailto:pa...@gf.microolap.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [BUGS] [HACKERS] COPY .... WITH (FORMAT binary) causes syntax error at or near "binary"
Hello, Robert. You wrote: RH> On Tue, Jul 5, 2011 at 11:06 AM, Alvaro Herrera RH> wrote: >> Excerpts from Pavel Golub's message of mar jul 05 10:52:06 -0400 2011: >>> Hello. >>> >>> System: PostgreSQL v9.0 Windows XP SP3 >>> SQL: COPY "tablename" TO STDOUT WITH (FORMAT binary) >>> ERROR: syntax error at or near "binary" >>> LINE 1: ...OPY "tablename" TO STDOUT WITH (FORMAT binary) >>> ^ >>> >>> ** Error ** >>> >>> ERROR: syntax error at or near "binary" >>> SQL state: 42601 >>> Character: 55 >>> >>> But if I use 'FORMAT text' or 'FORMAT csv' all is OK. >>> >>> Suppose this happens because BINARY is not listed in >>> "unreserved_keyword" neither in "col_name_keyword parser" parser rules, but >>> listed in "type_func_name_keyword" instead. >> >> That seems pretty unfortunate. Of course, it works if you quote it: >> >> COPY "tablename" TO STDOUT WITH (FORMAT "binary") >> >> I assume it's not in unreserved_keyword because it would cause a >> shift/reduce conflict elsewhere. RH> Yeah. In particular, it conflicts with the ancient copy syntax which RH> we still support for backwards compatibility with versions < 7.3. We RH> can fix the immediate problem with something like the attached. This patch is ugly. Sorry, Robert, but it's true. RH> (a) Should we do that? RH> (b) Should we back-patch it to 9.1 and 9.0? RH> (c) Should we consider removing compatibility with the ancient copy RH> syntax in 9.2, and de-reserving that keyword? (Given that the RH> workaround is this simple, I'm inclined to say "no", but could be RH> persuaded otherwise.) +1 for this. Pre-7.3 syntax is dead in fact for many years. -- With best wishes, Pavel mailto:pa...@gf.microolap.com -- 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] COPY .... WITH (FORMAT binary) causes syntax error at or near "binary"
Hello, Alvaro. You wrote: AH> Excerpts from Pavel Golub's message of mar jul 05 10:52:06 -0400 2011: >> Hello. >> >> System: PostgreSQL v9.0 Windows XP SP3 >> SQL: COPY "tablename" TO STDOUT WITH (FORMAT binary) >> ERROR: syntax error at or near "binary" >> LINE 1: ...OPY "tablename" TO STDOUT WITH (FORMAT binary) >> ^ >> >> ** Error ** >> >> ERROR: syntax error at or near "binary" >> SQL state: 42601 >> Character: 55 >> >> But if I use 'FORMAT text' or 'FORMAT csv' all is OK. >> >> Suppose this happens because BINARY is not listed in >> "unreserved_keyword" neither in "col_name_keyword parser" parser rules, but >> listed in "type_func_name_keyword" instead. AH> That seems pretty unfortunate. Of course, it works if you quote it: AH> COPY "tablename" TO STDOUT WITH (FORMAT "binary") AH> I assume it's not in unreserved_keyword because it would cause a AH> shift/reduce conflict elsewhere. Well, there are two ways: 1. Change documentation, so quoted or double quoted values are accepted 2. Fix parser -- With best wishes, Pavel mailto:pa...@gf.microolap.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] COPY .... WITH (FORMAT binary) causes syntax error at or near "binary"
Hello. System: PostgreSQL v9.0 Windows XP SP3 SQL: COPY "tablename" TO STDOUT WITH (FORMAT binary) ERROR: syntax error at or near "binary" LINE 1: ...OPY "tablename" TO STDOUT WITH (FORMAT binary) ^ ** Error ** ERROR: syntax error at or near "binary" SQL state: 42601 Character: 55 But if I use 'FORMAT text' or 'FORMAT csv' all is OK. Suppose this happens because BINARY is not listed in "unreserved_keyword" neither in "col_name_keyword parser" parser rules, but listed in "type_func_name_keyword" instead. -- With best wishes, Pavel mailto:pa...@gf.microolap.com -- 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] fixing PQsetvalue()
Hello, Merlin. You wrote: MM> On Jun 6 MM> (http://archives.postgresql.org/pgsql-hackers/2011-06/msg00272.php), MM> Pavel discovered an issue with PQsetvalue that could cause libpq to MM> wander off into unallocated memory that was present in 9.0.x. A MM> fairly uninteresting fix was quickly produced, but Tom indicated MM> during subsequent review that he was not happy with the behavior of MM> the function. Everyone was busy with the beta wrap at the time so I MM> didn't press the issue. MM> A little history here: PQsetvalue's MM> (http://www.postgresql.org/docs/9.0/static/libpq-misc.html) main MM> reason to exist is to allow creating a PGresult out of scratch data on MM> a result created via PQmakeEmptyResult(). This behavior works as MM> intended and is not controversial...it was initially done to support MM> libpqtypes but has apparently found use in other places like ecpg. MM> PQsetvalue *also* allows you to mess with results returned by the MM> server using standard query methods for any tuple, not just the one MM> you are creating as you iterate through. This behavior was MM> unnecessary in terms of what libpqtypes and friends needed and may (as MM> Tom suggested) come back to bite us at some point. As it turns out, MM> PQsetvalue's operation on results that weren't created via MM> PQmakeEmptyResult was totally busted because of the bug, so we have a MM> unique opportunity to tinker with libpq here: you could argue that you MM> have a window of opportunity to change the behavior here since we know MM> it isn't being usefully used. I think it's too late for that but it's MM> if it had to be done the time is now. MM> Pavel actually has been requesting to go further with being able to MM> mess around with PGresults (see: MM> http://archives.postgresql.org/pgsql-interfaces/2011-06/msg0.php), MM> such that the result would start to have some 'recordset' features you MM> find in various other libraries. Maybe it's not the job of libpq to MM> do that, but I happen to think it's pretty cool. Anyways, something MM> has to be done -- I hate to see an unpatched known 9.0 issue remain in MM> the wild. MM> merlin I confirm my desire to have delete tuple routine. And I'm ready to create\test\modify any sources for this. -- With best wishes, Pavel mailto:pa...@gf.microolap.com -- 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] Error in PQsetvalue
Hello, Merlin. You wrote: MM> On Wed, Jun 8, 2011 at 11:03 AM, Tom Lane wrote: >> Merlin Moncure writes: >>> On Wed, Jun 8, 2011 at 10:18 AM, Tom Lane wrote: Merlin Moncure writes: > I went ahead and tested andrew's second patch -- can we get this > reviewed and committed? >> Add it to the upcoming commitfest. >> >>> It's a client crashing bug in PQsetvalue that goes back to 9.0 :(. >> >> I was under the impression that this was extending PQsetvalue to let it >> be used in previously unsupported ways, ie, to modify a server-returned >> PGresult. That's a feature addition, not a bug fix. MM> It's neither -- it's documented libpq behavior: "The function will MM> automatically grow the result's internal tuples array as needed. MM> However, the tup_num argument must be less than or equal to PQntuples, MM> meaning this function can only grow the tuples array one tuple at a MM> time. But any field of any existing tuple can be modified in any MM> order. " MM> Andrew was briefly flirting with a proposal to tweak this behavior, MM> but withdrew the idea. >> it's a feature addition I approve of. I think serious consideration >> ought to be given to locking down returned results so PQsetvalue refuses >> to touch them, instead. Otherwise we're likely to find ourselves unable >> to make future optimizations because we have to support this >> barely-used-by-anybody corner case. Do I understand correctly that there is no any chance at all to have function like PQdeleteTuple in libpq? (see my message "PQdeleteTuple function in libpq" on Wed, 1 Jun 2011) MM> I think that's debatable, but I'm not going to argue this yea or nea. MM> But I will say that maybe we shouldn't confuse behavior issues with MM> bug fix either way...patch the bug, and we can work up a patch to lock MM> down the behavior and the docs if you want it that way, but maybe we MM> could bikeshed a bit on that point. MM> merlin -- With best wishes, Pavel mailto:pa...@gf.microolap.com -- 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] Error in PQsetvalue
Hello, guys. You wrote: MM> On Fri, Jun 3, 2011 at 10:36 PM, Andrew Chernow wrote: >> On 6/3/2011 10:26 PM, Andrew Chernow wrote: >>> > I disagree -- I think the fix is a one-liner. line 446: > if (tup_num == res->ntups&& !res->tuples[tup_num]) > > should just become > if (tup_num == res->ntups) > > also the memset of the tuple slots when the slot array is expanded can > be removed. (in addition, the array tuple array expansion should > really be abstracted, but that isn't strictly necessary here). > All true. This is a cleaner fix to something that was in fact broken ;) You want >>> >>> Attached a patch that fixes the OP's issue. PQsetvalue now uses pqAddTuple >>> to >>> grow the tuple table and has removed the remnants of an older idea that >>> caused >>> the bug. >>> >> >> Sorry, I attached the wrong patch. Here is the correct one. MM> This looks good. Pavel, want to test it? Sorry for delay in answer. Yeah, I'm glad to. Should I apply this patch by myself? MM> merlin -- With best wishes, Pavel mailto:pa...@gf.microolap.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Error in PQsetvalue
Hello. Reproduced under Windows XP SP3 using Visual C++ 2008 and Delphi. If PQsetvalue is called with second parameter equals to PQntuples then memory corruption appears. But it should grow internal tuples array and populate the last item with provided data. Please see the code: #include #include #include "libpq-fe.h" #pragma comment(lib,"libpq.lib") static void exit_nicely(PGconn *conn) { PQfinish(conn); exit(1); } int main(int argc, char **argv) { const char *conninfo; PGconn *conn; PGresult *res; if (argc > 1) conninfo = argv[1]; else conninfo = "dbname = postgres user = postgres password = password"; conn = PQconnectdb(conninfo); if (PQstatus(conn) != CONNECTION_OK) { fprintf(stderr, "Connection to database failed: %s", PQerrorMessage(conn)); exit_nicely(conn); } res = PQexec(conn, "SELECT generate_series(1, 10)"); if (!PQsetvalue(res, PQntuples(res), 0, "1", 1)) /* <- here we have memory corruption */ { fprintf(stderr, "Shit happens: %s", PQerrorMessage(conn)); exit_nicely(conn); } PQclear(res); PQfinish(conn); return 0; } BUT! If we change direct call to: ... res = PQexec(conn, "SELECT generate_series(1, 10)"); res2 = PQcopyResult(res, PG_COPYRES_TUPLES); if (!PQsetvalue(res2, PQntuples(res), 0, "1", 1)) { fprintf(stderr, "Shit happens: %s", PQerrorMessage(conn)); exit_nicely(conn); } ... then all is OK! As you can see, I copied result first. No error occurs. Can anybody check this on other platforms? -- Nullus est in vitae sensus ipsa vera est sensus. -- 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] PQdeleteTuple function in libpq
Hello, Andrew. You wrote: AC> On 6/2/2011 11:02 AM, Alvaro Herrera wrote: >> Excerpts from Andrew Chernow's message of jue jun 02 10:12:40 -0400 2011: >> Andrew, why we have PQmakeEmptyPGresult, PQcopyResult, PQsetResultAttrs, PQsetvalue and PQresultAlloc in this case? Of course there's no big deal with their absence but let's be consistent. >>> >>> I'm not entirely sure what you are trying to do, but can't you use >>> PQmakeEmptyPGresult, PQsetResultAttrs and PQsetvalue to construct a >>> result that excludes the tuples you don't want followed by a >>> PQclear(initial_result)? >> >> Seems pretty wasteful if you want to delete a single tuple from a large >> result. I think if you desired to compact the result to free some >> memory after deleting a large fraction of the tuples in the result it >> could be useful to do that, otherwise just live with the unused holes in >> the storage area as suggested by Pavel. >> AC> Another solution is to manually cursor through the set (like grab 1000 AC> tuples at a time) and copy the set to your own structure. That way, the AC> temporary double memory to perform the copy is not as big of a hit. By AC> using your own structure, you can organize the memory in a fashion that AC> is optimized for your requirement. I agree that there are a lot of possible solutions. But let me compare my function with official PQsetValue: 1. Both allow changing data in PGresult 2. Both allow changing tuples number 3. Both leave old data untouchable to be eliminated by PQClear 4. PQsetValue allocates more memory during work, mine not (it even may allow deleted tuple to be reused with a little fix) So why shouldn't we have both of them to make life easier? -- With best wishes, Pavel mailto:pa...@gf.microolap.com -- 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] PQdeleteTuple function in libpq
Hello, Alvaro. You wrote: AH> Excerpts from Andrew Chernow's message of jue jun 02 10:12:40 -0400 2011: >> > Andrew, why we have PQmakeEmptyPGresult, PQcopyResult, >> > PQsetResultAttrs, PQsetvalue and PQresultAlloc in this case? Of course >> > there's no big deal with their absence but let's be consistent. >> >> I'm not entirely sure what you are trying to do, but can't you use >> PQmakeEmptyPGresult, PQsetResultAttrs and PQsetvalue to construct a >> result that excludes the tuples you don't want followed by a >> PQclear(initial_result)? AH> Seems pretty wasteful if you want to delete a single tuple from a large AH> result. I think if you desired to compact the result to free some AH> memory after deleting a large fraction of the tuples in the result it AH> could be useful to do that, otherwise just live with the unused holes in AH> the storage area as suggested by Pavel. Totally! Thanks Alvaro. -- With best wishes, Pavel mailto:pa...@gf.microolap.com -- 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] PQdeleteTuple function in libpq
Hello, Andrew. You wrote: AC> On 6/2/2011 4:28 AM, Pavel Golub wrote: >> Hello, Andrew. >> >> You wrote: >> >> AC> On 6/1/2011 11:43 AM, Pavel Golub wrote: >>>> Hello. >>>> >>>> I'm some kind of PQdeleteTuple function will be very usefull in libpq. >>>> Because right now after deleting some record I need refetch result >>>> set, or mark tuple as deleted and this is headache for me. >>>> >> >> AC> IMHO, this should be handled by the application. You could track tuples >> AC> removed in an int[] or copy the result set into an application defined >> AC> array of C structures. I've always been under the impression that >> AC> PGresult objects are immutable once delivered to the application. >> >> >> Andrew, why we have PQmakeEmptyPGresult, PQcopyResult, >> PQsetResultAttrs, PQsetvalue and PQresultAlloc in this case? Of course >> there's no big deal with their absence but let's be consistent. >> AC> I'm not entirely sure what you are trying to do, but can't you use AC> PQmakeEmptyPGresult, PQsetResultAttrs and PQsetvalue to construct a AC> result that excludes the tuples you don't want followed by a AC> PQclear(initial_result)? Well, yes. I can. But don't you think it's too complex? Plus such approach uses twice as much memory. -- With best wishes, Pavel mailto:pa...@gf.microolap.com -- 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] BLOB support
Hello, Pavel. You wrote: PS> 2011/6/2 Peter Eisentraut : >> On ons, 2011-06-01 at 22:00 +0200, Radosław Smogura wrote: >>> I partialy implemented following missing LOBs types. Requirement for this >>> was >>> to give ability to create (B/C)LOB columns and add casting functionality >>> e.g. >>> SET my_clob = 'My long text'. >>> >>> Idea is as follow: >>> 0. Blob is two state object: 1st in memory contains just bytea, serialized >>> contains Oid of large object. >>> 1. Each type has additional boolean haslobs, which is set recursivly. >>> 2. Relation has same bool haslobs (used to speed up tables without LOBs) >>> 3. When data are inserted/updated then "special" function is called and >>> tuple >>> is modified in this way all LOBs are serialized to (old) LOB table and just >>> Oid is stored. >>> 4. When removed LOB is removed from (old) LOB table. >> >> Superficially, this looks like a reimplementation of TOAST. What >> functionality exactly do you envision that the BLOB and CLOB types would >> need to have that would warrant treating them different from, say, bytea >> and text? >> PS> a streaming for bytea could be nice. A very large bytea are limited by PS> query size - processing long query needs too RAM, LO (oid) solves this, doesn't it? PS> Pavel >> >> >> -- >> Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) >> To make changes to your subscription: >> http://www.postgresql.org/mailpref/pgsql-hackers >> -- With best wishes, Pavel mailto:pa...@gf.microolap.com -- 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] PQdeleteTuple function in libpq
Hello. So having studied the fe-exec.c sources, I came to this conclusion: we may just ignore deleted tuple and it will be destroyed by PQclear automatically, becuase PQclear deals with memory blocks. int PQdeleteTuple(PGresult *src, int tup_num) { if (!src) return NULL; int i, field; /* Invalid tup_num, must be < ntups */ if (tup_num < 0 || tup_num >= src->ntups) return FALSE; for (i = tup_num; i < src->ntups - 1; i++) { src->tuples[i] = src->tuples[i + 1]; } src->ntups--; return TRUE; } I also checked pqAddTuple, PQcopyResult and PQSetValue, they are OK with this solution. Am I correct with this? You wrote: PG> Hello. PG> I'm some kind of PQdeleteTuple function will be very usefull in libpq. PG> Because right now after deleting some record I need refetch result PG> set, or mark tuple as deleted and this is headache for me. PG> So I checked fe-exec.c sources and wrote this: PG> int PQdeleteTuple(PGresult *src, int tup_num) PG> { PG> if (!src) PG> return NULL; PG> int i, PG> field; PG> PG> /* Invalid tup_num, must be < ntups */ PG> if (tup_num < 0 || tup_num >= src->ntups) PG> return FALSE; PG> free(src->tuples[tup_num]); PG> PG> for (i = tup_num; i < src->ntups - 1; i++) PG> { PG> src->tuples[i] = src->tuples[i + 1]; PG> } PG> src->ntups--; PG> return TRUE; PG> } PG> But I'm pretty sure, that "free(src->tuples[tup_num])" is bullshit! PG> Because memory is allocated by pqResultAlloc, which in turn plays with PG> memory blocks and so on... PG> Can anyone help me in this? PG> PS I'm not a C guru, so don't please kick me hard. :) PG> Thanks. -- With best wishes, Pavel mailto:pa...@gf.microolap.com -- 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] PQdeleteTuple function in libpq
Hello, Andrew. You wrote: AC> On 6/1/2011 11:43 AM, Pavel Golub wrote: >> Hello. >> >> I'm some kind of PQdeleteTuple function will be very usefull in libpq. >> Because right now after deleting some record I need refetch result >> set, or mark tuple as deleted and this is headache for me. >> AC> IMHO, this should be handled by the application. You could track tuples AC> removed in an int[] or copy the result set into an application defined AC> array of C structures. I've always been under the impression that AC> PGresult objects are immutable once delivered to the application. Andrew, why we have PQmakeEmptyPGresult, PQcopyResult, PQsetResultAttrs, PQsetvalue and PQresultAlloc in this case? Of course there's no big deal with their absence but let's be consistent. -- With best wishes, Pavel mailto:pa...@gf.microolap.com -- 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] PQdeleteTuple function in libpq
Hello, Merlin. You wrote: MM> 2011/6/1 Pavel Golub : >> Hello. >> >> I'm some kind of PQdeleteTuple function will be very usefull in libpq. >> Because right now after deleting some record I need refetch result >> set, or mark tuple as deleted and this is headache for me. >> >> So I checked fe-exec.c sources and wrote this: >> >> int PQdeleteTuple(PGresult *src, int tup_num) >> { >> if (!src) >> return NULL; >> >> int i, >> field; >> >> /* Invalid tup_num, must be < ntups */ >> if (tup_num < 0 || tup_num >= src->ntups) >> return FALSE; >> >> free(src->tuples[tup_num]); >> >> for (i = tup_num; i < src->ntups - 1; i++) >> { >> src->tuples[i] = src->tuples[i + 1]; >> } >> src->ntups--; >> return TRUE; >> } >> >> But I'm pretty sure, that "free(src->tuples[tup_num])" is bullshit! >> Because memory is allocated by pqResultAlloc, which in turn plays with >> memory blocks and so on... >> >> Can anyone help me in this? >> >> PS I'm not a C guru, so don't please kick me hard. :) MM> well, you have PQaddTuple, but this was exposed mainly for the purpose MM> of building a PQresult from outside the libpq library -- not so much MM> to remove the 'constness' property of the PGResult. I have no MM> philosophical objection to making the PGresult able to be manipulated MM> in that fashion (although others might). From this point of view why we have PQmakeEmptyPGresult, PQcopyResult, PQsetResultAttrs, PQsetvalue and PQresultAlloc? If we have these functions I suppose we must have one more to delete (or hide) some tuples/attributes. MM> You could maybe just NULL MM> out tuples[i] and add some logic to various places to check that, like MM> in PQgetvalue. This is what I call headache. In this case to know rows number I cannot use PQntuples, but need to iterate through all tuples checking them for NULL or smth. MM> But before going down that road you need to make the case why this MM> should be handled in the library and not in your code -- PGresult MM> memory is slab allocated and therefore can only grow in size -- not MM> shrink and as such is not so much designed as a general purpose client MM> side dataset in the high level sense. Thinking of this I propose to hide tuples and not to eliminate\free them, because PQclear will free all PGResult resources. MM> merlin -- With best wishes, Pavel mailto:pa...@gf.microolap.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] PQdeleteTuple function in libpq
Hello. I'm some kind of PQdeleteTuple function will be very usefull in libpq. Because right now after deleting some record I need refetch result set, or mark tuple as deleted and this is headache for me. So I checked fe-exec.c sources and wrote this: int PQdeleteTuple(PGresult *src, int tup_num) { if (!src) return NULL; int i, field; /* Invalid tup_num, must be < ntups */ if (tup_num < 0 || tup_num >= src->ntups) return FALSE; free(src->tuples[tup_num]); for (i = tup_num; i < src->ntups - 1; i++) { src->tuples[i] = src->tuples[i + 1]; } src->ntups--; return TRUE; } But I'm pretty sure, that "free(src->tuples[tup_num])" is bullshit! Because memory is allocated by pqResultAlloc, which in turn plays with memory blocks and so on... Can anyone help me in this? PS I'm not a C guru, so don't please kick me hard. :) Thanks. -- With best wishes, Pavel mailto:pa...@gf.microolap.com -- 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] PostgreSQL Core Team
Hello. Congratulations! You wrote: DP> I'm pleased to announce that effective immediately, Magnus Hagander DP> will be joining the PostgreSQL Core Team. DP> Magnus has been a contributor to PostgreSQL for over 12 years, and DP> played a major part in the development and ongoing maintenance of the DP> native Windows port, quickly becoming a committer to help with his DP> efforts. He's one of the project's webmasters and sysadmins and also DP> contributes to related projects such as pgAdmin. In his spare time, he DP> serves as President of the Board of PostgreSQL Europe. DP> Regards, Dave. DP> -- DP> Dave Page DP> PostgreSQL Core Team DP> http://www.postgresql.org/ -- With best wishes, Pavel mailto:pa...@gf.microolap.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Warning during PostgreSQL 9.0.4 libpq.dll build on WinXP+MinGW
Hello, Pgsql-hackers. Today I built libpq.dll library on Windows XP using MinGW and got one warning message: Pablo@computer /z/pasha/postgresql-9.0.4/src/interfaces/libpq $ make ... gcc -O2 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -fno-strict-aliasing -fw rapv -DFRONTEND -DUNSAFE_STAT_OK -I. -I../../../src/include -I./src/include/port/win32 -DEXEC_BACKEND "-I../../../src/ include/port/win32" -I../../../src/port -I../../../src/port -DSO_MAJOR_VERSION=5 -c -o fe-connect.o fe-connect.c fe-connect.c: In function 'PQconnectPoll': fe-connect.c:1533:13: warning: unused variable 'on' ... Is it OK? -- With best wishes, Pavel mailto:pa...@gf.microolap.com -- 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] Please review test report form
Hello, Josh. You wrote: JB> All, JB> For 9.1, I'm trying to get beta testing a *bit* more organized in hopes JB> of shortening the beta period. Since we're not up and running on Django JB> on the main website yet, and thus I can't make an app for collecting JB> test reports, I've created a Google form: JB> http://tinyurl.com/3gp94er JB> Please provide some feedback on what we should be collecting JB> differently, if anything. JB> The idea is that results from this test form will be displayed in detail JB> and summary form so that hackers can refer to the test results. Among JB> other things, we particularly want to collect *positive* test results as JB> well as bugs so that we know how we're doing. Good enough for me! However "Steps to Reproduce" is more friendly name then "Parameters"... JB> -- JB> Josh Berkus JB> PostgreSQL Experts Inc. JB> http://pgexperts.com -- With best wishes, Pavel mailto:pa...@gf.microolap.com -- 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] MingW + GCC 4.5.2 + Relocate libpq.dll = SegFault
Hello, Charlie. Can you please express your opinion about my request "Warning compiling pg_dump (MinGW, Windows XP)" to pgsql-hackers on Thu, 13 Jan 2011. Do you have the same warnings using MinGW environment? You wrote: CS> I'm compiling postgresql 9.0.2 using msys + mingw + gcc 4.5.2 (latest CS> official release from mingw). This is on Windows 7 64-bit. CS> Unfortunately the built dlls, at least libpq.dll, crash if they need to CS> be relocated. This happens to me when loading libpq.dll into a project CS> that has a number of other dll requirements. Note this does NOT happen CS> when building with gcc 3.4.5. CS> Using GDB to track down the problem, the error occurs in CS> __gcc_register_frame and looks to be the same error described here: CS> http://old.nabble.com/Bad-DLL-relocation---reproducible-w--test-case-td18292380.html CS> Note a similar sounding error described, and fixed, in newer releases of CS> binutils (which mingw provides and I am using) is described here: CS> http://lists-archives.org/mingw-users/11369-error-0xc005-is-now-fixed.html CS> Looking at the postgresql Makefiles, the dlls are built using dllwrap. CS> In particular, see src/Makefile.shlib line 413 and src/Makefile.port CS> line 25. CS> When I change these to not use dllwrap and instead just use gcc -shared CS> the problem goes away. Therefore I'd like to propose: CS> 1. Change line 413 in src/Makefile.shlib CS> $(DLLWRAP) -o $@ --dllname $(shlib) $(DLLWRAP_FLAGS) --def CS> $(DLL_DEFFILE) $(OBJS) $(LDFLAGS) $(LDFLAGS_SL) $(SHLIB_LINK) CS> To: CS> $(CC) -shared -o $@ $(DLL_DEFFILE) $(OBJS) $(LDFLAGS) $(LDFLAGS_SL) CS> $(SHLIB_LINK) CS> 2. Changle line 73 in src/Makefile.port: CS> $(DLLWRAP) -o $@ --def $*.def $< $(LDFLAGS) $(LDFLAGS_SL) $(BE_DLLLIBS) CS> To: CS> $(CC) -shared -o $@ $*.def $< $(LDFLAGS) $(LDFLAGS_SL) $(BE_DLLLIBS) CS> I tested this by intentionally compiling libpq.dll using the link flag CS> -Wl,--image-base to make sure that its base address conflicted with CS> another dll loaded by my program. With the proposed changes, windows CS> successfully relocated libpq.dll without causing a segmentation fault. CS> I don't claim to know why dllwrap is producing dlls that can't be CS> relocated while gcc -shared is. But it appears to have been deprecated CS> back in 2006 according to the binutils mailing list: CS> http://www.mail-archive.com/bug-binutils@gnu.org/msg01470.html CS> So between being deprecated and not producing relocatable dlls, it seems CS> like its best to stop using dllwrap. If this seems like a reasonable CS> change I can put together a quick patch if that helps. CS> Thanks, CS> Charlie -- With best wishes, Pavel mailto:pa...@gf.microolap.com -- 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] Warning compiling pg_dump (MinGW, Windows XP)
Hello, Andrew. You wrote: AD> On 01/17/2011 03:51 PM, Tom Lane wrote: >> Andrew Dunstan writes: >>> On 01/17/2011 07:18 AM, Pavel Golub wrote: >>>> So you think I should just ignore these warnings? Because I can't >>>> remember the same behaviour on 8.x branches... >>> We've had them all along, as I said. See >>> <http://www.pgbuildfarm.org/cgi-bin/show_stage_log.pl?nm=frogmouth&dt=2011-01-04%2023%3A54%3A16&stg=make> >>> for the same thing in an 8.2 build. >> I wonder why mingw's gcc is complaining about %m when other versions of >> gcc do not? If you can't get it to shut up about that, there's not >> going to be much percentage in silencing warnings about %lld. >> >> AD> We could add -Wno-format to the flags. That makes it shut up, but maybe AD> we don't want to use such a sledgehammer. I want to understand the only thing. Are these warnings really dangerous? Or I should just ignore them? AD> cheers AD> andrew -- With best wishes, Pavel mailto:pa...@gf.microolap.com -- 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] Warning compiling pg_dump (MinGW, Windows XP)
Hello, Andrew. You wrote: AD> On 01/17/2011 05:54 AM, Pavel Golub wrote: >> Hello, Robert. >> >> You wrote: >> >> RH> 2011/1/13 Pavel Golub: >>>> Hello, Pgsql-hackers. >>>> >>>> I'm getting such warnings: >>>> >>>> pg_dump.c: In function 'dumpSequence': >>>> pg_dump.c:11449:2: warning: unknown conversion type character 'l' in format >>>> pg_dump.c:11449:2: warning: too many arguments for format >>>> pg_dump.c:11450:2: warning: unknown conversion type character 'l' in format >>>> pg_dump.c:11450:2: warning: too many arguments for format >>>> >>>> Line numbers my not be the same in the official sources, because I've >>>> made some changes. But the lines are: >>>> >>>> snprintf(bufm, sizeof(bufm), INT64_FORMAT, SEQ_MINVALUE); >>>> snprintf(bufx, sizeof(bufx), INT64_FORMAT, SEQ_MAXVALUE); >>>> >>>> In my oppinion configure failed for MinGw+Windows in this case. Am I >>>> right? Can someone give me a hint how to avoid this? >> RH> It seems like PGAC_FUNC_SNPRINTF_LONG_LONG_INT_FORMAT is getting the >> RH> wrong answer on your machine, though I'm not sure why. The easiest >> RH> workaround is probably to run configure and then edit >> RH> src/include/pg_config.h before compiling. >> >> Thanks Robert. What value should I enter for this option? AD> Mingw has always had a huge number of format warnings. See for example AD> <http://www.pgbuildfarm.org/cgi-bin/show_stage_log.pl?nm=frogmouth&dt=2011-01-17%2007%3A30%3A00&stg=make> So you think I should just ignore these warnings? Because I can't remember the same behaviour on 8.x branches... AD> If someone wants to fix them that would be good, but I'm not sure it's a AD> simple task. There's probably some discussion of it in the archives back AD> when we first did the Windows port. AD> cheers AD> andrew -- With best wishes, Pavel mailto:pa...@gf.microolap.com -- 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] Warning compiling pg_dump (MinGW, Windows XP)
Hello, Robert. You wrote: RH> 2011/1/13 Pavel Golub : >> Hello, Pgsql-hackers. >> >> I'm getting such warnings: >> >> pg_dump.c: In function 'dumpSequence': >> pg_dump.c:11449:2: warning: unknown conversion type character 'l' in format >> pg_dump.c:11449:2: warning: too many arguments for format >> pg_dump.c:11450:2: warning: unknown conversion type character 'l' in format >> pg_dump.c:11450:2: warning: too many arguments for format >> >> Line numbers my not be the same in the official sources, because I've >> made some changes. But the lines are: >> >> snprintf(bufm, sizeof(bufm), INT64_FORMAT, SEQ_MINVALUE); >> snprintf(bufx, sizeof(bufx), INT64_FORMAT, SEQ_MAXVALUE); >> >> In my oppinion configure failed for MinGw+Windows in this case. Am I >> right? Can someone give me a hint how to avoid this? RH> It seems like PGAC_FUNC_SNPRINTF_LONG_LONG_INT_FORMAT is getting the RH> wrong answer on your machine, though I'm not sure why. The easiest RH> workaround is probably to run configure and then edit RH> src/include/pg_config.h before compiling. Thanks Robert. What value should I enter for this option? -- With best wishes, Pavel mailto:pa...@gf.microolap.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Warning compiling pg_dump (MinGW, Windows XP)
Hello, Pgsql-hackers. I'm getting such warnings: pg_dump.c: In function 'dumpSequence': pg_dump.c:11449:2: warning: unknown conversion type character 'l' in format pg_dump.c:11449:2: warning: too many arguments for format pg_dump.c:11450:2: warning: unknown conversion type character 'l' in format pg_dump.c:11450:2: warning: too many arguments for format Line numbers my not be the same in the official sources, because I've made some changes. But the lines are: snprintf(bufm, sizeof(bufm), INT64_FORMAT, SEQ_MINVALUE); snprintf(bufx, sizeof(bufx), INT64_FORMAT, SEQ_MAXVALUE); In my oppinion configure failed for MinGw+Windows in this case. Am I right? Can someone give me a hint how to avoid this? Thanks in advance -- With best wishes, Pavel mailto:pa...@gf.microolap.com -- 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] Cannot compile Pg 9.0.2 with MinGW under Windows
Thanks, Andrew. I'll check my environment one more time. You wrote: AD> On 12/23/2010 07:11 AM, Pavel Golub wrote: >> Hello, Pgsql-bugs. >> >> Tried to use MinGw under windows to build client libraries at least. >> However failed on "./configure --withou-zlib" stage. >> >> Please find attached log file, stdout and stderr outputs. >> >> The main problem here I suppose is >> "configure: WARNING:.h: present but cannot be compiled" >> >> Please five me advice on this. >> Thanks in advance AD> Your gcc doesn't look like others we have: AD> You have: AD> gcc (GCC) 3.4.4 (msys special) AD> Copyright (C) 2004 Free Software Foundation, Inc. AD> This is free software; see the source for copying conditions. There AD> is NO AD> warranty; not even for MERCHANTABILITY or FITNESS FOR A PARTICULAR AD> PURPOSE. AD> configure:3252: $? = 0 AD> configure:3259: gcc -v >&5 AD> Reading specs from /usr/lib/gcc/i686-pc-msys/3.4.4/specs AD> Configured with: /home/cstrauss/build/gcc3/gcc-3.4.4/configure AD> --prefix=/usr --sysconfdir=/etc --localstatedir=/var AD> --infodir=/share/info --mandir=/share/man --libexecdir=/lib AD> --enable-languages=c,c++ --disable-nls --enable-threads=posix AD> --enable-sjlj-exceptions --enable-hash-synchronization AD> --enable-libstdcxx-debug --with-newlib AD> Thread model: posix AD> Buildfarm narwhal has: AD> gcc.exe (GCC) 3.4.2 (mingw-special) AD> Copyright (C) 2004 Free Software Foundation, Inc. AD> This is free software; see the source for copying conditions. There is NO AD> warranty; not even for MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. AD> configure:3252: $? = 0 AD> configure:3259: gcc -v>&5 AD> Reading specs from c:/MinGW/bin/../lib/gcc/mingw32/3.4.2/specs AD> Configured with: ../gcc/configure --with-gcc --with-gnu-ld AD> --with-gnu-as --host=mingw32 --target=mingw32 --prefix=/mingw AD> --enable-threads --disable-nls AD> --enable-languages=c,c++,f77,ada,objc,java AD> --disable-win32-registry --disable-shared --enable-sjlj-exceptions AD> --enable-libgcj --disable-java-awt --without-x AD> --enable-java-gc=boehm --disable-libgcj-debug --enable-interpreter AD> --enable-hash-synchronization --enable-libstdcxx-debug AD> Thread model: win32 AD> gcc version 3.4.2 (mingw-special) AD> Buildfarm frogmouth has: AD> gcc.exe (GCC) 4.5.0 AD> Copyright (C) 2010 Free Software Foundation, Inc. AD> This is free software; see the source for copying conditions. There is NO AD> warranty; not even for MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. AD> configure:3252: $? = 0 AD> configure:3259: gcc -v>&5 AD> Using built-in specs. AD> COLLECT_GCC=c:\mingw\bin\gcc.exe AD> AD> COLLECT_LTO_WRAPPER=c:/mingw/bin/../libexec/gcc/mingw32/4.5.0/lto-wrapper.exe AD> Target: mingw32 AD> Configured with: ../gcc-4.5.0/configure AD> --enable-languages=c,c++,ada,fortran,objc,obj-c++ AD> --disable-sjlj-exceptions --with-dwarf2 --enable-shared AD> --enable-libgomp --disable-win32-registry --enable-libstdcxx-debug AD> --enable-version-specific-runtime-libs --disable-werror --build=mingw32 --prefix=/mingw AD> Thread model: win32 AD> gcc version 4.5.0 (GCC) AD> cheers AD> andrew -- With best wishes, Pavel mailto:pa...@gf.microolap.com -- 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] [FeatureRequest] Base Convert Function
Hello. Guys, guys! It was only a joke! :) Please accept my appologies. Anyway I find such function usefull even though I still hadn't situation when it might be needed. You wrote: AD> On 12/21/2010 04:28 PM, Pavel Golub wrote: >> >> PS> * It isn't a typical and often request, >> PS> * There are not hard breaks for custom implementation, >> PS> * You can use plperu or plpython based solutions, >> PS> * It's not part of ANSI SQL >> >> But MySQL has such function. What's wrong with us? ;) >> AD> Our aim is not to duplicate everything in MySQL. AD> cheers AD> andrew -- With best wishes, Pavel mailto:pa...@gf.microolap.com -- 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] [FeatureRequest] Base Convert Function
Hello, Pavel. You wrote: PS> Hello PS> Dne 21. prosince 2010 21:11 Tomáš Mudruňka napsal(a): >> >> Thx for you answers :-) >> Well... i know that i can write my own plugin and i am familiar with C so >> this is not the problem, but i think that such feature should be >> implemented directly in PgSQL because there are already functions for >> converting to/from base 16 so why don't make this more flexible and >> generalize it to any other radix? It's quite simple to do and i don't see >> any reason why 16 should be there and 8, 32 or 36 shouldn't :-) >> PS> * It isn't a typical and often request, PS> * There are not hard breaks for custom implementation, PS> * You can use plperu or plpython based solutions, PS> * It's not part of ANSI SQL But MySQL has such function. What's wrong with us? ;) PS> Regards PS> Pavel Stehule >> peace >> >> On Tue, 21 Dec 2010 15:04:03 +0100, Florian Pflug wrote: >>> On Dec21, 2010, at 12:48 , Robert Haas wrote: 2010/12/21 Tomáš Mudruňka : > Is there possibility of having internal base converting function in > PgSQL? > There are already functions for converting between decimal and > hexadecimal > notations i think pgsql can be able to convert between number with > radixes > from 1 to 36 (actually fast (de)encoding base36 is what i need)... It should be pretty easy to write such a function in C, perhaps using strtol() or strtoul(). >>> >>> If you're not comfortable doing this in C, you might also want to >> consider >>> one of procedural languages pl/pgsql, pl/perl, pl/python. pl/pgsql is >>> probably >>> only viable if you just need this for ints and bigints, unless you don't >>> care about performance. >>> >>> best regards, >>> Florian Pflug >> >> -- >> S pozdravem >> Best regards >> Tomáš Mudruňka - Spoje.net / Arachne Labs >> >> XMPP/Jabber: har...@jabbim.cz, ICQ: 283782978 >> >> -- >> Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) >> To make changes to your subscription: >> http://www.postgresql.org/mailpref/pgsql-hackers >> -- With best wishes, Pavel mailto:pa...@gf.microolap.com -- 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] add label to enum syntax
Hello, Alvaro. You wrote: AH> Excerpts from Pavel Golub's message of jue oct 28 07:50:24 -0300 2010: >> Forgot link to poll: >> http://pgolub.wordpress.com/2010/10/28/poll-alter-type-enumtype-add-what-newlabel/ AH> Hah, there are 17 votes as of right now, no option is below 23% and no AH> option is above 29%. Yeah, right now 42 votes: VALUE 26% LABEL 26% Just ADD 'newlabel' 24% ELEMENT 21% MEMBER 2% -- With best wishes, Pavel mailto:pa...@gf.microolap.com -- 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] add label to enum syntax
Hello, Andrew. You wrote: AD> It occurred to me in the dead of the night that instead of: AD> ALTER TYPE enumtype ADD 'newlabel' AD> it might be better to have: AD> ALTER TYPE enumtype ADD LABEL 'newlabel' AD> That way if we later wanted to support some other sort of ADD operation AD> on types we would be able to more easily. LABEL is already a keyword, so AD> it should be pretty minimally invasive to make this change, and if we AD> want to do it now is the time. AD> Thoughts? AD> cheers AD> andrew Forgot link to poll: http://pgolub.wordpress.com/2010/10/28/poll-alter-type-enumtype-add-what-newlabel/ -- With best wishes, Pavel mailto:pa...@gf.microolap.com -- 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] add label to enum syntax
Hello, Andrew. You wrote: AD> It occurred to me in the dead of the night that instead of: AD> ALTER TYPE enumtype ADD 'newlabel' AD> it might be better to have: AD> ALTER TYPE enumtype ADD LABEL 'newlabel' AD> That way if we later wanted to support some other sort of ADD operation AD> on types we would be able to more easily. LABEL is already a keyword, so AD> it should be pretty minimally invasive to make this change, and if we AD> want to do it now is the time. AD> Thoughts? AD> cheers AD> andrew I'm with you. BTW, I wrote post with poll about this. -- With best wishes, Pavel mailto:pa...@gf.microolap.com -- 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] Why do we have a database specification in .pgpass?
Hello, Bruce. You wrote: BM> We have a database specification in .pgpass: BM> hostname:port:database:username:password BM> What is the purpose of 'database' since username/password combinations BM> are global, not per database? I would like to documents its purpose. BM> -- BM> Bruce Momjian http://momjian.us BM> EnterpriseDB http://enterprisedb.com BM> + It's impossible for everything to be true. + For future use? -- With best wishes, Pavel mailto:pa...@gf.microolap.com -- 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] Windows Tools
Hello, David. We are still using MinGW. Why? How? And other questions are answered in my post: http://pgolub.wordpress.com/2008/12/15/building-postgresql-client-library-using-mingw-under-winxp-sp3/ You wrote: DF> Hello, DF> At work, I've been tasked with providing some Windows connection DF> libraries, etc. Are people still using MinGW, or should I just DF> recommend using a proprietary toolkit to do the builds? If so, which DF> one(s)? DF> Cheers, DF> David (Windows n00b) DF> -- DF> David Fetter http://fetter.org/ DF> Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter DF> Skype: davidfetter XMPP: david.fet...@gmail.com DF> iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics DF> Remember to vote! DF> Consider donating to Postgres: http://www.postgresql.org/about/donate -- With best wishes, Pavel mailto:pa...@gf.microolap.com -- 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] regclass without error?
Hello, guys. You wrote: TI> Hi, TI> Is there any way to use regclass without having ERROR? TI> pgpool-II needs to find the oid from table name and for the purpose it TI> issues something like "SELECT 'table_name'::regproc::oid". Problem is, TI> if the table does not exist, an error occured and the transaction TI> aborts. Ideally if the table does not exist, the SELECT returns 0 TI> (InvalidOid). Agreed with Tatsuo about having InvalidOid. TI> Any idea? TI> -- TI> Tatsuo Ishii TI> SRA OSS, Inc. Japan TI> English: http://www.sraoss.co.jp/index_en.php TI> Japanese: http://www.sraoss.co.jp -- With best wishes, Pavel mailto:pa...@gf.microolap.com -- 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] keepalive in libpq using
Hello, Fujii. You wrote: FM> On Wed, Jul 7, 2010 at 10:04 PM, Robert Haas wrote: >> On Tue, Jul 6, 2010 at 1:08 PM, Pavel Golub wrote: >>> While I'm very excited about enabling keepalives in libpq, I want to >>> know how can I use this functionality in my application? >>> >>> Let's imagine that I connect to a server with keepalives option, other >>> options (keepalives_idle, keepalives_interval, keepalives_count) are >>> used either. Then network goes down. So, how will I know that >>> connection is dead? Any callback function? Or should I check PQstatus >>> periodically? >> >> I'm not sure, exactly. I think what'll happen is that if you're >> trying to read data from the remote server, the connection will >> eventually break instead of hanging forever, but I'm not exactly sure >> what that'll look like at the libpq level. I'm not sure what effect >> it'll have on an idle connection. FM> When network goes down while receiving result from server, you will FM> get PGRES_FATAL_ERROR from PQresultStatus(). Also you can get the FM> error message "could not receive data from server: Connection timed out" FM> via PQerrorMessage(). Sounds good for me. My customer proposed such a scenario: I have opened connection to database server (ver 8.4.3) through the SSH tunnel. This tunnel is created by external program "PUTTY". My PC running Application is connected to the ETH switch and server is connected to another port of the switch. So, when I disconnect server from the switch, my PC is still online (I mean ETH port have the link). So, my local side of the SSH tunnel is still alive, but remote side is down... So no connection to server is possible at this moment. But in this scenario, when I do something like this: PQExec(...); Application stay locked on this command. Looks like client is still waiting answer from the server in the case the TCP connection is still alive, even if Server is not accessible. The question is: Can this situation be solved with keealives? FM> Regards, -- With best wishes, Pavel mailto:pa...@gf.microolap.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] keepalive in libpq using
Hello all. While I'm very excited about enabling keepalives in libpq, I want to know how can I use this functionality in my application? Let's imagine that I connect to a server with keepalives option, other options (keepalives_idle, keepalives_interval, keepalives_count) are used either. Then network goes down. So, how will I know that connection is dead? Any callback function? Or should I check PQstatus periodically? Thank in advance -- Nullus est in vitae sensus ipsa vera est sensus. -- 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] Keepalives win32
Hello, Tom. You wrote: TL> Bruce Momjian writes: >> Tom Lane wrote: >>> What's your idea of "affecting the fewest people"? There is no previous >>> history to be backward-compatible with, because we never supported >>> keepalive on Windows before. >> Well, starting in 9.0, keepalives in libpq will default to 'on': TL> Yes, which is already a change in behavior. I don't understand why you TL> are worrying about "backwards compatibility" to parameter values that TL> weren't in use before. I think self-consistency of the new version is TL> far more important than that. Absolutely agree. >> even if we use Windows defaults, those defaults might be different for >> different Windows versions. TL> I'm not sure if that's an issue or not, but if it is, that seems to me TL> to argue for #2 not #1. TL> regards, tom lane -- With best wishes, Pavel mailto:pa...@gf.microolap.com -- 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] Keepalives win32
Hello, Magnus. You wrote: MH> 2010/6/30 Pavel Golub : >> Hello, Bruce. >> >> You wrote: >> >> BM> Tom Lane wrote: >>>> Robert Haas writes: >>>> > On Mon, Jun 28, 2010 at 8:24 PM, Tom Lane wrote: >>>> >> What I was trying to say is I think we could dispense with the >>>> >> setsockopt() code path, and just always use the WSAIoctl() path anytime >>>> >> keepalives are turned on. I don't know what "system default values" >>>> >> you're speaking of, if they're not the registry entries; and I >>>> >> definitely don't see the point of consulting such values if they aren't >>>> >> user-settable. We might as well just consult the RFCs and be done. >>>> >>>> > FWIW, I think I prefer Magnus's approach, but I'm not 100% sure I can >>>> > defend that preference... >>>> >>>> Well, basically what I don't like about Magnus' proposal is that setting >>>> one of the two values changes the default that will be used for the >>>> other one. (Or, if it does not change the default, the extra code is >>>> useless anyway.) If we just always go through the WSAIoctl() path then >>>> we can clearly document "the default for this on Windows is so-and-so". >>>> How is the documentation going to explain the behavior of the proposed >>>> code? >> >> BM> Let's look at the usage probabilities. 99% of Win32 users will not use >> BM> any of these settings. >> >> Let me disagree with this statement. As DAC developer I'm faced with >> opposite reality. There are a lot of users demanding this >> functionality. MH> It's very intersting to hear from somebody who expects to actually use MH> this. But are you aware that we're only talking about *adjusting* the MH> keepalive values, not enabling them? Because we will, as the code MH> stands now, enable keepalive by defaults - just use the system default MH> values for timeout intervals. (Meaning this is how we do it on Unix as MH> of HEAD, irregardless of my patch) MH> Do you have an opinion on the two choices for handling keepalives_idle MH> and keepalives_interval? They basically are: MH> 1) When not configured, use system defaults. When only one of the two MH> parameters configured, use RFC default for the other one (overwrite MH> system default). MH> 2) When not configured, use RFC defaults (overwrite system defaults). MH> When only one of the two parameters configured, use RFC default for MH> the other one (overwrite system default) MH> 3) When not configured, use system defaults. When only one of the two MH> parameters configured, throw error. MH> I can see pros and cons with both. Given that I still think *most* MH> people will not configure the intervals at all, I think #1 is the one MH> that follows "principle of least surprise". Perhaps option *3* is the MH> one that does this for partial configuration? Frankly speaking I cannot decide what is the best approach. :) It's up to you guys. -- With best wishes, Pavel mailto:pa...@gf.microolap.com -- 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] Keepalives win32
Hello, Bruce. You wrote: BM> Tom Lane wrote: >> Robert Haas writes: >> > On Mon, Jun 28, 2010 at 8:24 PM, Tom Lane wrote: >> >> What I was trying to say is I think we could dispense with the >> >> setsockopt() code path, and just always use the WSAIoctl() path anytime >> >> keepalives are turned on. I don't know what "system default values" >> >> you're speaking of, if they're not the registry entries; and I >> >> definitely don't see the point of consulting such values if they aren't >> >> user-settable. We might as well just consult the RFCs and be done. >> >> > FWIW, I think I prefer Magnus's approach, but I'm not 100% sure I can >> > defend that preference... >> >> Well, basically what I don't like about Magnus' proposal is that setting >> one of the two values changes the default that will be used for the >> other one. (Or, if it does not change the default, the extra code is >> useless anyway.) If we just always go through the WSAIoctl() path then >> we can clearly document "the default for this on Windows is so-and-so". >> How is the documentation going to explain the behavior of the proposed >> code? BM> Let's look at the usage probabilities. 99% of Win32 users will not use BM> any of these settings. Let me disagree with this statement. As DAC developer I'm faced with opposite reality. There are a lot of users demanding this functionality. BM> I would hate to come up with a solution that BM> changes the default behavior for that 99%. BM> Therefore, I think using hard-coded defaults only for cases where BM> someone sets one but not all settings is appropriate. The documentation BM> text would be: BM> On Windows, if a keepalive settings is set, then defaults will be BM> used for any unset values, specifically, keepalives_idle (200) and BM> keepalives_interval(4). Windows does not allow control of BM> keepalives_count. BM> Seems simple enough. BM> -- BM> Bruce Momjian http://momjian.us BM> EnterpriseDB http://enterprisedb.com BM> + None of us is going to be here forever. + -- With best wishes, Pavel mailto:pa...@gf.microolap.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Bug in CREATE FUNCTION with character types if RETURNS TABLE used
Hello. Already sent this to psql-bugs, but noticed one more issue. Since the first issue is critical for me as a developer, the second one confuses my clients (and me a little) ;) PostgreSQL version: 8.4.x Operating system: All == 1 ISSUE = If RETURNS TABLE clause of CREATE FUNCTION contain column of character type (without length specifier) it should be treated as character(1) according to manual, but it look like text. (http://www.postgresql.org/docs/8.4/static/datatype-character.html) Example: CREATE OR REPLACE FUNCTION test_char_function() RETURNS TABLE(id int, salesourcecode character) AS $BODY$ VALUES (1, 'one'), (2, 'two'), (3, 'three'); $BODY$ LANGUAGE 'sql' pqfmod function from libpq library for salesourcecode column returns 0, which is wrong. Who can ever imagine bpchar of length 0? :) (http://www.postgresql.org/docs/8.4/static/libpq-exec.html#LIBPQ-EXEC-SELECT-INFO) == 2 ISSUE = If RETURNS TABLE clause of CREATE FUNCTION contain column of char or varchar type with length specifier it still acts like text. CREATE OR REPLACE FUNCTION test_char_function() RETURNS TABLE(id int, salesourcecode varchar(30)) AS $BODY$ VALUES (1, 'one'), (2, 'two'), (3, 'three'); $BODY$ LANGUAGE 'sql'; pqfmod function from libpq library for salesourcecode column returns 0, which is wrong. -- With best wishes, Pavel mailto:pa...@gf.microolap.com -- 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] PQftype implementation
Hello, Tom. Yes, you are absolutely right. My bad! Sorry guys! :) You wrote: TL> Pavel Golub writes: >> Here I created user-defined type "my_varchar" for internal tests. But >> PQftype returns 1043 (varchar oid) for the "info" column. TL> Really? I tried it and got 172069, which is about right for where the TL> OID counter is in my database. I think you messed up your test. TL> res = PQexec(conn, "select * from my_varchar_test"); TL> if (PQresultStatus(res) != PGRES_TUPLES_OK) TL> { TL> fprintf(stderr, "SELECT failed: %s", PQerrorMessage(conn)); TL> PQclear(res); TL> exit_nicely(conn); TL> } TL> nFields = PQnfields(res); TL> for (i = 0; i < nFields; i++) TL> printf("%-15s %d\n", PQfname(res, i), PQftype(res, i)); TL> regards, tom lane -- With best wishes, Pavel mailto:pa...@gf.microolap.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] PQftype implementation
Hello, Pgsql-hackers. The script: CREATE TYPE my_varchar; CREATE OR REPLACE FUNCTION my_varcharout(my_varchar) RETURNS cstring AS 'varcharout' LANGUAGE 'internal' IMMUTABLE STRICT COST 1; CREATE OR REPLACE FUNCTION my_varcharin(cstring, oid, integer) RETURNS my_varchar AS 'varcharin' LANGUAGE 'internal' IMMUTABLE STRICT COST 1; CREATE TYPE my_varchar (INPUT=my_varcharin, OUTPUT=my_varcharout, DEFAULT='', INTERNALLENGTH=-1, ALIGNMENT=int4, STORAGE=EXTENDED, TYPMOD_IN=varchartypmodin, TYPMOD_OUT=varchartypmodout); CREATE TABLE my_varchar_test( id serial primary key, info my_varchar(100) ) Here I created user-defined type "my_varchar" for internal tests. But PQftype returns 1043 (varchar oid) for the "info" column. I'm a little bit confused of such behaviour. What am I missing? Ans where in the sources can I find the way server fills res->attDescs[field_num].typid? Thanks in advance. -- With best wishes, Pavel mailto:pa...@gf.microolap.com -- 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] bytea vs. pg_dump
Hello, Bernd. You wrote: BH> --On Dienstag, Juli 07, 2009 18:07:08 -0400 Tom Lane BH> wrote: >> Enum. If we do this then it seems entirely fair that someone might >> want other settings someday. Also, it seems silly to pick a format >> partly on the grounds that it's expansible, and then not make the >> control GUC expansible. Perhaps >> >> SET bytea_output = [ hex | traditional ] BH> I like the enum much better, too, but BH> SET bytea_output = [ hex | escape ] BH> looks better to me (encode/decode are using something like this already). BH> -- BH> Thanks BH> Bernd Yeah, this looks nice for me too -- With best wishes, Pavel mailto:pa...@gf.microolap.com -- 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] Named transaction
Hello. You wrote: TL> Pavel Golub writes: >> Is there any possibility that Postgres will have named transaction >> ever, like Firebird? TL> What in heck is a named transaction, and why should we care? TL> regards, tom lane Sorry guys, my bad. The thing is I'm not a Firebird adept either and this is my customer's maggot. Anyway, it's interesting, so I'll try to explain. 1. He has one connection 2. He has several scripts executed simultaneously (in different threads I think) 3. Each script must be executed inside it's own transaction In Firebird (Interbase) he may have several transactions per one connection each with it's own isolation level and parameters. Each transaction in Firebird has ID (or name?). That's why I called it "named transaction". :) Moreover Firebird allows nested transactions (http://wiki.firebirdsql.org/wiki/index.php?page=IN+AUTONOMOUS+TRANSACTION) Now to implement customer's desire in PostgreSQL there are two ways: 1. Each script must be executed in the separate connection context 2. Each script must be executed inside critical section, in other words current scipt must block others until COMMIT or ROLLBACK I don't like both. How I imagine "named transactions". :) START TRANSACTION first; INSERT ; -- inside first START TRANSACTION second; DELETE ; -- inside second SWITCH TRANSACTION first; -- switch context INSERT ; -- inside first COMMIT; -- first terminated, default context active (no transaction) SWITCH TRANSACTION second; -- activate second transaction context COMMIT; -- second terminated, no context active So, what do you think guys? -- With best wishes, Pavel mailto:pa...@gf.microolap.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Named transaction
Hello, pgsql-hackers. Is there any possibility that Postgres will have named transaction ever, like Firebird? Now for each transaction client should open separate connection. But CONNECTION LIMIT option for database make this a little bit harder -- With best wishes, Pavel mailto:pa...@gf.microolap.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Keep alive in libpq
Hello, postgresmen. I found solution how to implement keep alive through sockets in archive: http://archives.postgresql.org/pgsql-interfaces/2006-11/msg00014.php However, it is dated 2006 year and I am wonder if this is for real? At least in Windows environment? If not are there any solutions? -- With best wishes, Pavel mailto:pa...@gf.microolap.com -- 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] [PATCH] Borland C Compiler compatibility issues
On Mon, Apr 20, 2009 at 1:38 AM, Tom Lane wrote: > Pavel Golub writes: >> Here the patch to /src/include/pg_config_os.h attached improving >> Borland C++ Compiler compatibility. > > Applied along with your other two patches. Please note in future that > pg_config_os.h is a derived file --- this patch should have been against > src/include/port/win32.h. > > regards, tom lane > Oops. My bad. Thanks. -- Nullus est in vitae sensus ipsa vera est sensus. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] [PATCH] Borland C Compiler compatibility issues
Here the patch to /src/interfaces/libpq/bcc32.mak attached improving Borland C++ Compiler compatibility. Issues described here: http://pgolub.wordpress.com/2009/04/13/building-postgresql-client-library-using-borland-c-compiler-bcc-under-winxp/ This patch changes order of include folders placing ..\..\include\port\win32_msvc on the first place. Thus src\include\port\win32_msvc\dirent.h is used instead of incompatible $(BCB)\include\dirent.h -- Nullus est in vitae sensus ipsa vera est sensus. bcc32.mak.patch Description: Binary data -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] [PATCH] MinGW compatibility issues
Here the patches to /src/include/libpq/libpq-be.h and /src/interfaces/libpq/libpq-int.h attached improving MinGW compatibility. Issues described here: http://pgolub.wordpress.com/2008/12/15/building-postgresql-client-library-using-mingw-under-winxp-sp3/ This patch adds missing includes with appropriate ifdef check -- Nullus est in vitae sensus ipsa vera est sensus. libpq-be.h.patch Description: Binary data libpq-int.h.patch Description: Binary data -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] [PATCH] Borland C Compiler compatibility issues
Here the patch to /src/include/pg_config_os.h attached improving Borland C++ Compiler compatibility. Issues described here: http://pgolub.wordpress.com/2009/04/13/building-postgresql-client-library-using-borland-c-compiler-bcc-under-winxp/ This patch defines missing constants needed by /port/dirent.c and /port/open.c -- Nullus est in vitae sensus ipsa vera est sensus. pg_config_os.patch Description: Binary data -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers