Re: [GENERAL] What is unsecure postgres languages? How to disable them?
Hi, Am 22.02.2010 11:56, schrieb dipti shah: Hi, Could anyone please tell me what is unsecure postgres languages(like C, pgperl, pgpython??). How to disable them or restrict them only for super user? They are already restricted for the super user because of their insecure nature. That means those languages allow you full access to the system (and even some innards of postgresql) with the rights of the postgresql process. You can remove the language handlers: http://www.postgresql.org/docs/8.1/static/app-droplang.html If you wish. Apart from that there is no more risk attached to them unless you are super user or write insecure functions with them then say with the copy command. Regards Tino Wildenhain smime.p7s Description: S/MIME Cryptographic Signature
Re: [GENERAL] Is It Good Practice That I use TableName-Month-Year Convention
Hi, Am 13.01.2010 09:16, schrieb Yan Cheng Cheok: I realize the READ performance goes down dramatically when my table goes large. Every new day goes on, my table can increase x millions of new rows. I was wondering whether this is good practice I can design my database in this way? Instead of having lot- unit- measurement Can I have lot-March-2010- unit-March-2010- measurement-March-2010 lot-April-2010- unit-April-2010- measurement-April-2010 (1) That's mean in my stored procedure, I need to dynamically generate the table name. Is this the dynamic SQL to correct way, to dynamically generate table name : http://www.postgresql.org/docs/8.1/interactive/ecpg-dynamic.html (2) Is this consider a good approach, to overcome speed problem (especially read speed). Any potential problem I should put an eye on, before I implement this strategy? You might combine this approach with table partitioning to give you a cleaner view to your data like this: http://www.postgresql.org/docs/current/static/ddl-partitioning.html in your situation it would probably make sense to put the actual partitiones into a separate schema to keep your main work area clean from clutter. HTH Tino smime.p7s Description: S/MIME Cryptographic Signature
Re: [GENERAL] Session based transaction!!
Hi, S Arvind schrieb: Hi Everyone, In a web application running in Tomcat and Postgres 8.3 as DB, i need to know whether my given task is feasible or not. All the db operation which is done by that web application must be rollback at the end(session dies) and the DB data must be same as the starting stage(of session). Its like virtually placing the data for that session alone and rollbacking the db to the template which is originally been. So whenever users get in the webapplication, the initial data must be the template data only and he can perform any operation for which data is visible for that user alone and when the users leaves(session destroyed) all the data changed in that time must be rollbacked. I thought this by, When the session created in the application a transaction must be started and all the activites must be done on the DB, but the transaction will not be commited or rollback across request but it must be done across the session. By not destroying the connection and using it for all the work done by that session. when session destroy we can rollback the entire transaction Is there any other way to achieve the requirements in postgres. Thanks in advance.. Isn't that the default? If not you should handle your database interaction with some high priority handler which runs first and ends last in your request and handles all exceptions bubbling from other activities inside it and does a rollback in this case. (Thats the way Zope does it - but it has a transaction framework. Not sure if Tomcat offers the same easy hooks but there should be a way.) Regards Tino smime.p7s Description: S/MIME Cryptographic Signature
Re: [GENERAL] Allowing for longer table names (64 characters)
Hi Allan, Am 20.11.2009 10:42, schrieb Allan Kamau: ... Thanks Andreas, I too agree it may not be a good idea to have long for various reasons including porting/upgrading issues and so on, as I have many tables, I seem to have been caught up in describing table functionality in the table name :-) As Andreas said, comment is perhaps a better place for descriptions. Also did you thought of using schema as additional grouping system to avoid having many overly descriptive table names? Regards Tino smime.p7s Description: S/MIME Cryptographic Signature
Re: R: [GENERAL] Field's position in Table
Alvaro Herrera wrote: Michael Gould wrote: ... doesn't need to look at the overflow page. I don't know if this is true or not in PostGres. If it isn't then I'm not sure what difference it would make other than allowing for pretty documentation. Postgres does not overflow pages. Tuples are stored wholly on a single page. If they don't fit, large attributes are stored in a separate table (the TOAST table) and only a pointer is kept in the main table. So reordering won't give you that benefit. The other difference it would make is that it'd open the door for optimizations like storing all fixed-length not nullable attributes together at the start of the tuple. That should give slightly better performance. And which is quite easily done by: BEGIN; CREATE table reorder_footable AS SELECT b,c,a FROM footable; DROP TABLE footable; ALTER TABLE reorder_footable RENAME TO footable; COMMIT; yes of course this does not deal with FK correctly so a lot more work would need to be done for a general solution - but in some cases it should be all one needs for the tuple optimization. I personally don't by the prettyness argument for reordering columns since for all practical use I prefer SELECT a,b,c over SELECT * Regards Tino smime.p7s Description: S/MIME Cryptographic Signature
Re: [GENERAL] Number Conversion Function
justin wrote: Tom Lane wrote: Tino Wildenhain t...@living-examples.com writes: I would not recommend to do this within the database. Thats typical a job for your presentation layer. ... but having said that, I think the money datatype has a function for this. Whether that's of any use to you I dunno; money is pretty restrictive about what it can handle. regards, tom lane I disagree the database is the wrong place, there are cases it makes sense. Which cases would that be? Regards Tino -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Postgres: Starting Server in background mode
CM J wrote: Hi, I do not want start postgres as a service.Postgres will bundled along with my application and i am only looking at starting it only from cmd line.If there are any options to disable this cmd window which appears after executing the pg_ctl.exe start, that would be great ! The problem here is, Postgres is not an embedded database but really a database management system. Therefore trying to bundle it with a desktop application will usually cause more headaches. I'd suggest installing Postgres as central service (as you know with web servers, application servers, mail servers... ) and connect your clients to it or use a desktop/linkable database for example firebird or whatever. Regards Tino -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Number Conversion Function
Abdul Rehman wrote: Hi all, Can any body help me in converting numeric values into words via postgres function: i.e. 313 to THREE HUNDRED THREE I would not recommend to do this within the database. Thats typical a job for your presentation layer. Regards Tino -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] sql transaction
Alvaro Herrera wrote: Andreas Kretschmer wrote: Alvaro Herrera alvhe...@commandprompt.com wrote: Andreas Kretschmer wrote: Jasid ZA za.ja...@gmail.com wrote: Hi, Can we use sql transactions(BEGIN, ROllBACK, COMMIT etc) in a postgresql function(user defined) which is written in PL/Perl? No. A function is an autonomous transaction. You can use savepoints. This question comes up very often. Would somebody please write it and a detailed answer to add to the FAQ in the Wiki? I would do it, but unfortunately my english is too bad... So add it to the German FAQ, and ask someone to translate it to english? Of course its already in the documentation - not sure if the posters of this question in the mail already read the documentation, the faq or much less checked the mail archive ;-) But this sounds like fun, I'll check how to add something to the documentation :-) Regards Tino smime.p7s Description: S/MIME Cryptographic Signature
Re: [GENERAL] Uploading data to postgresql database
Subha Ramakrishnan wrote: Hi All, I wanted to know if there is a way to upload kml/gpx data directly to the DB. By upload you mean...? You can always just insert the date opaque as raw field or text or use large object (lo). So far, I have been using shp2pgsql to upload data from shape files. I don't want to convert it to shape and then upload it. If someone knows of a way to upload without converting to shape file, please let me know. If you plan to do anything with the data inside the database (and your reference to PostGIS indicates that) you need to insert it in a structured way as opposed to just upload the data. What is wrong with the conversion step you mention? Regards Tino smime.p7s Description: S/MIME Cryptographic Signature
Re: [GENERAL] Imagenes
Hi Lius, Luis Cevallos wrote: Saludos Cordiales. Mi nombre es Luis Cevallos y tengo muchas dudas de como hacer para guardar una imagen desde php hasta una tabla que tiene campo oid no lo logro hacer claro estoy usando ADODB pero no se como hacerlo. it would be easier for us to help you if you could stick to the language of the page where you subscribed to the mailing list :-) (Unless this is localized nowadays?) e.g. English :-) Since most of us (including me) do not understand Spain. Your question seems to target the ways to store images for your application. There are several ways to do this which have their own respective advantages - disadvantages: - in the database as lo (large object) pro: - data store in sync with metadata - common backup (yes with some limitations) - accessible over common service (e.g. database connection) con: - interface to lo a little more complicated and not always well supported - backup more difficult - large binary data over database connection - in the database as raw pro: - data store in sync with metadata - common backup - accessible over common service (e.g. database connection) - easy access via sql con: - large binary data over database connection - file size limit of about 2G - in the filesystem, metadata (e.g. location) in database pro: - easy to implement - high troughput to and from fileystem - possible to deliver via FS access, e.g. with apache con: - backup needs to take care of the file system - overwrite and locking needs to be carefully considered - not accessible from single connection (extra service to access the files needed) - can easily get out of sync with the database (metadata w/o file or vice versa) HTH Tino smime.p7s Description: S/MIME Cryptographic Signature
Re: [GENERAL] alter multiple tables
Hi, Kodok Marton wrote: Hello, I have in every table columns like: username character varying(20) NOT NULL I want to extend the length of varchar in all tables. next time you should probably consider using a domain type (or stick to text) Since I have a lot of tables and mirrored backups, I am wondering if there is a way to alter automatically all tables where colname matches 'username' Is there a way to do this? It should be possible to generate a list of tables either via query or using pg_dump -L with grep and create SQL based on this (with a script, unix shell) and execute it against the database. (Test this of course) HTH Tino smime.p7s Description: S/MIME Cryptographic Signature
Re: [GENERAL] Transactions in user defined function
Jasid ZA wrote: Hi, Can we use sql transaction(BEGIN, COMMIT, REVOKE) inside a user defined function in Postgresql 8.3? Would that part of the documentation help? http://www.postgresql.org/docs/8.3/interactive/plpgsql-structure.html Functions and trigger procedures are always executed within a transaction established by an outer query — they cannot start or commit that transaction, since there would be no context for them to execute in. so in short: no. Regards Tino smime.p7s Description: S/MIME Cryptographic Signature
Re: [GENERAL] Transactions in user defined function
Grzegorz Jaśkiewicz wrote: On Fri, Mar 13, 2009 at 12:03 PM, Jasid ZA za.ja...@gmail.com wrote: Hi, Can we use sql transaction(BEGIN, COMMIT, REVOKE) inside a user defined function in Postgresql 8.3? looking forward to hear from you Nope. If function does something naughty - do RAISE EXCEPTION, that will break transaction and query (hence rollback). Please notice, that in plpgsql function uses BEGIN and END, and those are precisely there to start and end transaction. Sorry, they are not: It is important not to confuse the use of BEGIN/END for grouping statements in PL/pgSQL with the similarly-named SQL commands for transaction control. PL/pgSQL's BEGIN/END are only for grouping; they do not start or end a transaction. Regards Tino smime.p7s Description: S/MIME Cryptographic Signature
Re: [GENERAL] Postgres Cookbook
Greg Smith wrote: On Wed, 4 Mar 2009, Artacus wrote: So it looks like at one time we had a cookbook. But the links are dead now. I'm not sure why Roberto Mello stopped hosting that, but you can see the last content posted there at http://web.archive.org/web/20031207045017/http://www.brasileiro.net/postgres/cookbook/ Even though that is mainly aimed at older versions, there are a lot of neat PL/PGSQL examples there that you might wrangle into working against a current one. I just found that I registered a matching named domain at some time... if there is content to host, I could probably jump in. Regards Tino smime.p7s Description: S/MIME Cryptographic Signature
Re: [GENERAL] Running untrusted sql safely?
Scott Marlowe wrote: On Sun, Feb 15, 2009 at 4:39 PM, Christophe x...@thebuild.com wrote: On Feb 15, 2009, at 2:47 PM, Stuart McGraw wrote: I just hoping for some confirmation that the permissions based approach did not have some holes in it that I am not seeing. Another possibility is to create a set of functions that contain the query operations you would like to allow, isolate those in a schema, and make that schema the only thing accessible to the (semi-)trusted users. I can see that getting complex real fast in a big operation, but for a database that runs a few big reporting queries every day or sits on an intranet would be workable. ... And to actually answer Christophes question: yes, granting only SELECT to a few tables is enough to prevent them doing anything else in the database. But watch out for the default permissions on the public schema of all the databases the users are able to connect to. Regards Tino smime.p7s Description: S/MIME Cryptographic Signature
Re: [GENERAL] Need some help converting MS SQL stored proc to postgres function
Hi, Mike Christensen wrote: Hi guys, I'm in the process of migrating my database from MS SQL 2005 to PostgreSQL and there's one final stored proc that's giving me some problems.. Perhaps someone can give me some help? Here's the sproc: SELECT RecipeId, Avg(Rating) as Rating INTO #ratings FROM RecipeRatings GROUP BY RecipeId UPDATE Recipes SET Rating = #ratings.Rating FROM Recipes INNER JOIN #ratings ON (#ratings.RecipeId = Recipes.RecipeId AND #ratings.Rating Recipes.Rating) would not UPDATE receipes SET rating = r.rating FROM (SELECT recipeid,avg(rating) as rating GROUP BY recipeid) r WHERE recipeid=r.recipeid AND rating r.rating work too w/o temp table? (untested, can contain errors) Tino smime.p7s Description: S/MIME Cryptographic Signature
Re: [GENERAL] auto insert data every one minute
Hi, searchelite wrote: ... i can use pg_sleep..but i have thousands of data to be inserted..is there any better way using pg_sleep? I wonder what is you complete problem? It seems all the advices given so far are shots-in-the-dark. Could you perhaps expand a bit? Also for sophisticated solution, if you stick to windows you might want to consider something different then just pure CMD, say some scripting language to support. Tino smime.p7s Description: S/MIME Cryptographic Signature
Re: [GENERAL] DB and Unicode problem (was: user and DB confusion)
arnuld uttre wrote: ... and I phpBB can connect to the DB now but with a new problem from phoBB3:The database you have selected was not created in UNICODE or UTF8 encoding. Try installing with a database in UNICODE or UTF8 encoding. what to do about it ? Well exactly that. What information is missing? There is a parameter on createdb command line (As well as CREATEDB command in SQL) which needs to be set to unicode. Hint: to get collation right, you might want to initdb first (with empty $PGDATA) with correct locale settings (including charset UTF-8 which will then be the default encoding for all subsequent created databases. HTH Tino smime.p7s Description: S/MIME Cryptographic Signature
Re: [GENERAL] access public relation from a private database
Joey K. wrote: Hello, I've created a table in public schema, CREATE TABLE public.foo (fooid SERIAL); When I try to create a table in database nowhere that references public.foo table, CREATE DATABASE nowhere; \c nowhere; CREATE TABLE bar (bar integer REFERENCES public.foo(fooid)); I get, ERROR: relation public.foo does not exist. Can I reference public relations from private database? You cannot. But you can create a private schema along the public one above in the same database. Regards Tino smime.p7s Description: S/MIME Cryptographic Signature
Re: [GENERAL] how can I find out the numeric directory name of each database in PostgreSQL 8.3
Hi, Justin Yao wrote: Hi, In PostgreSQL 7.x, I can use SQL: select datname, oid from pg_database to find out the numeric directory name under $PGDATA/base for each database. But it doesn't work for PostgreSQL 8.3. Is there any way I can do it for 8.3? What would you do with that name once you have it? Tino smime.p7s Description: S/MIME Cryptographic Signature
Re: [GENERAL] match an IP address
Phoenix Kiula wrote: My post at the bottom. ... No. You have no idea what the design is for. Not forum crap. What happens when you need to store in a table the activity log? ACTIVITY_ID USER_STAMP (currently user_id or ip for registered and unregistered resp.) And here it gets wrong. Obviously you would store the session id or if you have a lot of relations, use a sequence generated key for session_id (compare with my design in the other post, in this case session_id would be serial and you'd have a field session_key text with the index for the cookies in the sessions table instead) The idea of storing IP for users is already being done. So what? Abandon this idea I'd say. Its based on the wrong asumption IP addresses map to users in 1:1 relation. Everytime they do something, you do not store their IP. Why would you? Just store their user id. For unregistered ones however, we store the IP because there is nothing else. There is no user ID for them. What's your logic for getting a user ID for unregistered guys -- invent one automagically? Finally, this SQL: WHERE COALESCE(user_id, to_char(access_ip)) = matchstring; Ever tried this crap on a table of 10 million records on a live website, where this query is happening at 3000 times per second? No You have 10 million people active the same time in your site? such function schtick will match the raw speed of a simpler indexed query. Or did you mean my index should contain the COALESCE already? Tino, I wasn't talking about sessions. This is not about session IDs. Well actually this is. You are just naming it differently. A session ID is useless the next time a certain IP address does something on the site. You want a commonality for non-registered users across many different sessions. (Apart from the fact that session IDs are usually long hashes which take up space in the table and in the index) Yes but only active ones. btw, given IP is in every request, where is your username coming from? Apart from basic auth, there is no way of having a userid tied to the request directly, so how are you doing this? Tino smime.p7s Description: S/MIME Cryptographic Signature
Re: [GENERAL] match an IP address
Hi, Phoenix Kiula wrote: Please forgive my attempt to help you based on a woefully insufficient description of your problem and situation. I will not make any attempt to do so again. To others: thanks for your suggestions, but this issue is not one of session IDs, nor is it solved by storing IP addresses separately (which does not assume 1:1 correlation between user and IP). We'll let that be. Let's just say that in *many* online situations it is vital for querying speed to have the same column that stores users -- both registered and unregistered. A query in SQL that matches against an IP if not registered, where is the user coming from? The IP is clearly not an identifier for a user. You (and the OP) should disregard that idea. address regexp to identify the unregistered ones may work for some with smaller databases, which is great, and if it doesn't (the ~ match is simply not practical for large busy websites), then consider a small separate column that stores the registration status as a flag. The user id itself would serve as that flag. If non NULL - user known, otherwise unknown. Sounds easy, no? No regex at all! :) Thanks. Thx ;) Tino smime.p7s Description: S/MIME Cryptographic Signature
Re: [GENERAL] match an IP address
Craig Ringer wrote: Phoenix Kiula wrote: 1. What extra tax will this constraint levy on an INSERT or UPDATE on this table? There are about 100,000 inserts a day, and over three times as many UPDATES. The concurrency is pretty high -- I mean sometimes 1,000 users at the same time but no more than that. If the additional cost of insertion/updating is not too heavy, I suppose this could be a nice approach. ... I'd try a functional index first. If that didn't do the job, I'd use a trigger-maintained column _purely_ as an optimisation (ie I could drop it and lose no data) that stored text representations of the data. Honestly, though, I expect the functional index would be more than good enough and probably wouldn't have much of an INSERT/UPDATE cost. Again, of course, I'd test before setting anything in stone. And do not forget you know beforehand if you are going to lookup a user or an IP. So I fail to see what problem needs to be solved here :( Maybe we can get an enlightenment on where every data comes from and whats going to be its usage? Maybe we can then work out a better solution at all? Peephole optimizing is great but sometimes the effect is better if you just open the door :-) Tino smime.p7s Description: S/MIME Cryptographic Signature
Re: [GENERAL] PL/Python - Execute return results
Hi, Dean Grubb wrote: Hi, plan = plpy.prepare(SELECT last_name FROM my_users WHERE first_name = $1, [ text ]) rv = plpy.execute(plan, [ name ], 5) return rv[last_name] If the SELECT command does not return any results, how do I catch/check for this? I'm surprised to find you directly accessing attributes but reading the documentation I see that plpy does not support db api 2.0 style so things are a little bit different. Did you test: if rv: ? I think this should work because in python empty lists (and result sets) would compare to False. Cheers Tino smime.p7s Description: S/MIME Cryptographic Signature
Re: [GENERAL] match an IP address
Hi, Joao Ferreira gmail wrote: well... my IP addresses are stored in a TEXT type field. that field can actually contain usernames like 'joao' or 'scott' and it can contain IP addresses Any reason not to change this in the first place? For a quick fix you could use regex to find the records likely containing an ip-address notation. Tino smime.p7s Description: S/MIME Cryptographic Signature
Re: [GENERAL] match an IP address
Phoenix Kiula wrote: my IP addresses are stored in a TEXT type field. that field can actually contain usernames like 'joao' or 'scott' and it can contain IP addresses I think this is common DB design on many websites that have registered user IDs. Is it? Name one! Sounds like crappy design to me. My humble suggestion would be to make another column in the table called user_registered or something. Make this an int(1). If the user is registered, value is 1, otherwise 0. Maybe registration (aka authentication) is mixed up with simple session handling? To update your existing data onetime, run an SQL update query looking for IP pattern with 3 dots (which is likely IP address and not user id) or by the INET conditions in previous suggestions. It could also just be a fdn (hostname) - still I'm a bit puzzled how a username and an IP-address can get into the same field. Next, include the columns user_id and user_registered in the same index. This won't increase the size of the index too much, as the user_registered field is a small INT number. But from now on your SQL queries: ...WHERE user_id = 'testuser' AND user_registered = 1 will return much faster. I have found this to the most convenient and fastest solution in PGSQL instead of haivng INET in the WHERE clauses. Can you give example on where the inet entry is going to be used? Cheers Tino smime.p7s Description: S/MIME Cryptographic Signature
Re: [GENERAL] [OT] CSS Mailinglist?
Hi Michelle, Michelle Konzack wrote: Hello, I am changeing my website from crappy HTML Tables to CSS :-D and need some help but failed to find mailinglists for it. Does someone from you know one? I have yet to see a competent mailinglist on HTML/CSS but if websites are ok, I'd recomment http://alistapart.com/ (not every article out there but some are really helpful). HTH Tino smime.p7s Description: S/MIME Cryptographic Signature
Re: [GENERAL] [pgadmin-support] PostgreSQL
Mickey Shekdar wrote: Hello, We are switching our web booking engine vendor. The present vendor is using Microsoft SQL and the new vendor uses PostgreSQL. I have three questions and will appreciate your comments: [1] Will PostgreSQL perform better on Linux or Windows OS The performance is better on Linux, last but not least because it is developed and thus optimized there much longer then it is natively available for windows. Also system management is a lot more easy on unix like systems. [2] What is the best RAID configuration/number of dives you recommend You should ask the vendor of your web booking engine, it really depends much on the nature of the transactions. Also have a look in the archive of the postgresql general - list, which is also much more appropriate then this list here. [3] Is there a hosting company offering fully monitored/managed PostgreSQL Yes, plenty. Please check the webpage of postgresql.org Cheers Tino Wildenhain smime.p7s Description: S/MIME Cryptographic Signature
Re: [GENERAL] Oracle and Postgresql
Henry wrote: On Sun, August 31, 2008 10:44 pm, Scott Marlowe wrote: The other thing that holds back PostgreSQL right now is a lack of experienced pgsql DBAs and application developers. That will change over time. And built-in, simple to use, reliable, flexible and fast replication. Many a Pg admin or implementer has looked on with envy at what Oracle does out of the box in terms of replication alone. Yes, there are Well actually it appears a lot of DBAs are not satisfied with Oracles out of the box solution and buy third party stuff when it comes to replication. So this argument doesn't really bite. Regards Tino smime.p7s Description: S/MIME Cryptographic Signature
Re: [GENERAL] SQL optimization - WHERE SomeField STARTING WITH ...
Hi Bill, Bill wrote: The SQL database servers I have worked with cannot use and index for a SELECT of the form SELECT * FROM ATABLE WHERE AFIELD LIKE ? because there is no way to know the location of the wild card until the parameter value is known. InterBase and Firebird allow SELECT * FROM ATABLE WHERE AFIELD STARTING WITH ? which is equivalent to LIKE 'ABC%' and will use an index on AFIELD. Is there a similar syntax in PostgreSQL? Yes, its actually: LIKE 'ABC%' and it will use an index. Regards Tino smime.p7s Description: S/MIME Cryptographic Signature
Re: [GENERAL] SQL optimization - WHERE SomeField STARTING WITH ...
Bill wrote: Tino Wildenhain wrote: Hi Bill, Bill wrote: The SQL database servers I have worked with cannot use and index for a SELECT of the form SELECT * FROM ATABLE WHERE AFIELD LIKE ? because there is no way to know the location of the wild card until the parameter value is known. InterBase and Firebird allow SELECT * FROM ATABLE WHERE AFIELD STARTING WITH ? which is equivalent to LIKE 'ABC%' and will use an index on AFIELD. Is there a similar syntax in PostgreSQL? Yes, its actually: LIKE 'ABC%' and it will use an index. Regards Tino Are you saying that a parameterized query whose WHERE clause is AFIELD LIKE ? will use an index on AFIELD if the parameter value is 'ABC%'. I ... no, I'm not saying that anymore (nor did I intend to do :-) I was just misreading your question. Sorry. Regards Tino smime.p7s Description: S/MIME Cryptographic Signature
Re: [GENERAL] Postgre connect on Postgre
Hi, Anderson dos Santos Donda wrote: Thanks man!! I'll study this module!! You should also be aware that sometimes instead of connecting two separate databases via dblink or similar, two schemas in one database can be used instead. It really depends on what you are really doing if there are even more solutions (like slony for example). Regards Tino PS: the database likes to be called Postgresql or Postgres :-) On Tue, Aug 26, 2008 at 11:19 PM, Christophe [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] wrote: On Aug 26, 2008, at 7:10 PM, Anderson dos Santos Donda wrote: How I can connect a postgre database on another postgre database, and manipulate the datas on both database? There is a module in contrib just for such a purpose: http://www.postgresql.org/docs/8.3/interactive/dblink.html -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org mailto:pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general smime.p7s Description: S/MIME Cryptographic Signature
Re: [GENERAL] Dumping/Restoring with constraints?
Hi, Phoenix Kiula wrote: Hello. I have a database dump. With data and schema, which includes all the constraints and rules. But it seems the pg_dumpall command does not copy data in such a way that foreign keys are satisfied upon restoring. Because tables are inter-related, importing them keep giving errors and eventually no data is imported. Neither pg_dumpall nor pg_restore seems to have a without constraints or delay constraints check type command. What am I missing? Thanks for any advice. Apart from disabling triggers temporarely, if you have enough space in xlog, you could try to add BEGIN work; to the start of the restore SQL and COMMIT; a the end. Regards Tino smime.p7s Description: S/MIME Cryptographic Signature
Re: [GENERAL] Easy upgrade on Cpanel *without* downtime
Hi, Phoenix Kiula wrote: Hi. I have googled and googled for good, simple instructions to upgrade from 8.2.3 to 8.3.3 (latest stable at this time?) I am on a Cpanel interface. Use Apache and PHP for most of my websites. This seems to be the most often quoted resource on forums etc: http://kb.linuxnetworkcare.com/node/21 But this suggests backing up, then UNinstalling (downtime and loss of settings!), then REinstalling postgresql. Is there no sensible way of simply upgrading the database engine without affecting either the uptime or the data itself? I really really do not wish to backup gigabytes worth of data and then reupload it back into the DB. This represents a downtime of *at least* an hour or so, which our busy website doesn't have. Any thoughts or pointers? The usual way to do it and to avoid downtime is to install the new version along the old one, backup/restore as usual (e.g. pg_dump | pg_restore to avoid wasting space 2 times) then run slony to permanently update the last bits while you are checking settings and performance of the new version. If everything is fine, use a calm moment to switch your configuration to use the new database in production. Regards Tino smime.p7s Description: S/MIME Cryptographic Signature
Re: [GENERAL] Easy upgrade on Cpanel *without* downtime
Andrew Sullivan wrote: On Mon, Aug 25, 2008 at 11:34:00AM +0200, Tino Wildenhain wrote: (e.g. pg_dump | pg_restore to avoid wasting space 2 times) then run slony to permanently update the last bits If you're going to run slony, then the pg_dump|pg_restore step is completely wasted. Slony will restore all the data again. You do need to run pg_dump -s, of course. good point :-) Regards Tino smime.p7s Description: S/MIME Cryptographic Signature
Re: [GENERAL] just work installation configuration
Hi, Yuri Huitron Alvarado wrote: Hi! I'm a newbie and want to startup my just installed postgres : * 8.3-community * SPARC * Solaris 10 it answers that it has to find postgresql.conf but I can't find it anywere do I have to write it specifying the most basic options? I cant say much about your package but since it seems to have a start script (also I remember solaris even had its own postgres for system purposes?) so what you might need to do is to find a place where the database should be and run initdb with the options you need. (man initdb) This will create the database cluster as well as postgresql.conf and pg_hba.conf in the datadir. Usually packages should come with a README or something telling you about specific setup if you need to do anything special. Regards Tino Wildenhain smime.p7s Description: S/MIME Cryptographic Signature
Re: [GENERAL] LIKE and SIMILAR TO
Hi, c k wrote: Hello all, As we are migrating our ERP application from MySQL to PostgreSQL we have some difficulties. One of them is use of Like and Similar to operators. We often use LIKE to search a string from front-end without case sensetivity. As postgreSQL's LIKE is case sensitive, we tried ILIKE and SIMILAR TO, but both are slower than LIKE and we must need case insensitivity. How can we get this by increases speed. All search columns are VARCHAR(100)to VARCHAR(250). Currently without index. You could build an index on lower(column) and use lower(column) like ... this would speed up queries with exact match as well as 'foo%' e.g. start with... match. Make sure when you create the database cluster (initdb) you used the currect locale, otherwise lower() (and ilike) probably do not work as you might expect. For any more complex searches I'd recommend full text index, for example tsearch2. Regards Tino smime.p7s Description: S/MIME Cryptographic Signature
Re: [GENERAL] SELECT query experts, anyone?
Teemu Juntunen wrote: Hi Experts, is threre any way to SELECT values in columns instead of rows? For example select products and their manufacters in the followin way: product1; manufacturer1; manufacturer2;,,, manufacturerN product2; manufacturer3; product3; manufacturer1;.. manufacturerN-1 With a function you could put the product manufacturers in one string, but I would like to have them in columns. How about arrays. Is there a way to SELECT values in an array to columns? This should work: SELECT product,array(SELECT manufacturer FROM manufacturers WHERE manufacturer_id=products.manufacturer_id) WHERE ... or something along the lines of the above. Regards Tino smime.p7s Description: S/MIME Cryptographic Signature
Re: [GENERAL] CASE
c k wrote: Hi, I want to create a function as follows: case variable=value1 SQL statements case variable=value2 SQL statements case else end of case Looks like you want to use pl/pgsl and IF ... THEN ... ELSE instead? Cheers Tino smime.p7s Description: S/MIME Cryptographic Signature
Re: [GENERAL] [Q] DNS(bind) ER model
Roderick A. Anderson wrote: Anyone aware of an ER model for holding name server records? Working on the zone file data and I am getting close but keep running into the differences between MX records (with a priority) and the others that can hold either a domain/sub-domain/host name or an IP address depending on whether is an A, TXT, PTR, etc. or a CNAME. Much of the database will be populated and changed automagically so the controller for the application will do the right thing but humans will get involved every so often. I hope I can get the database to make the right thing easy and the wrong thing impossible for them. Any suggestions? looks like you want to write your own powerdns ? :-) http://www.powerdns.com/ Greets Tino smime.p7s Description: S/MIME Cryptographic Signature
Re: [GENERAL] size of a table on postgresql
aravind chandu wrote: Hello, The following is the procedure to calculate the disk space occupied by postgresql from a flat file. In this I didn't understood some terms 24 bytes: each row header (approximate) 24 bytes: one int field and one text field + 4 bytes: pointer on page to tuple here row header is taken as 24 bytes and in some sites it is given row header as 40 bytes and in some 32 bytes http://www.sbras.ru/rus/docs/db/postgres/doc/howto/faq-english.shtml#3.6 http://www.softlab.ntua.gr/facilities/documentation/unix/postgres/faq-english.html Is the row header value is constant or it is variable?If so what could be the maximun value? what is that additional +4bytes as pointer on page to tuple. Should all these were fixed or is there any command to find the values for these fields? Maybe you want: http://www.postgresql.org/docs/8.3/interactive/functions-admin.html pg_relation_size(text) Disk space used by the table or index with the specified name. The table name can be qualified with a schema name pg_total_relation_size(text) Total disk space used by the table with the specified name, including indexes and toasted data. The table name can be qualified with a schema name ? Cheers Tino smime.p7s Description: S/MIME Cryptographic Signature
Re: [GENERAL] automatic REINDEX-ing
Martijn van Oosterhout wrote: ... AIUI, people know VACUUM FULL sucks and that in the cases where it really helps CLUSTER is faster anyway and doesn't have the index problems. The TODO list reference several discussions on the topic. Or, failing that, what's the reason to not issue a REINDEX CONCURRENTLY automatically after a VACUUM FULL (or something to that effect)? Or how about not doing VACUUM FULL at all. It's not a command that should be run regularly in most situations. Which makes me think if the solution would be to just run CLUSTER under the hood when VACUUM FULL is requested. Would that introduce any other problems? Cheers Tino smime.p7s Description: S/MIME Cryptographic Signature
Re: [GENERAL] Fwd: setting datestyle
C K wrote: Dear Friends, I have tried to set datestyle to 'DMY'. As per manual and many other posts related to this point, I have set datestyle to 'DMY' at database. but still it is needed to execute each time *set datestyle to 'DMY' *before starting any new connection. Once this is set, there is no problem for that connection till it was live. Is there any solution to this problem? Why even after setting database property datastyle to 'DMY' gives an error when *set datestyle to 'DMY' *is not executed at starting connection? Maybe you have a setting at database or login level? ALTER ROLE ... SET ... can be effective or ALTER DATABASE ... SET These would override whatever you set in the config file. Also config file changes only take effect when you restart the cluster in most cases. Regards Tino smime.p7s Description: S/MIME Cryptographic Signature
Re: [GENERAL] php + postgresql
Hi, Aarni Ruuhimäki wrote: On Friday 25 July 2008 15:33, you wrote: I would avoid that in favour of using $HOME/.pgpass http://www.postgresql.org/docs/8.1/interactive/libpq-pgpass.html HTH Tino Hi, Quite right you are. Or something like this? require(/eg/unknown_path/deep_somewhere_else/dbconnect_app_name.php) Well this would be reinventing the wheel and also can really cause accidently checking that into your version control system which should be avoided for credentials holding files. T. smime.p7s Description: S/MIME Cryptographic Signature
Re: [GENERAL] php + postgresql
Joshua D. Drake wrote: On Sat, 2008-07-26 at 11:13 +0930, admin wrote: Anyway, while I'm quite happy to continue banging out things that just work in PHP for the time being, you suggest (in a subsequent post) that there is one scripting language in particular that you'd use ... might I enquire which language that is, and why? Just curious, I'm definitely not looking for an ideological debate. You do realize that you just opened one of the longest, loudest and most inherently beer inducing arguments known to man since Emacs vs Vi? (answer: Joe) So why not! I use Python. I love Python. Although I guarantee you that others will say ruby, perl, java (well maybe not java). I'd say python too but I intentionally left that out in the discussion just to avoid that usual foo vs. bar discussion which isn't to win. The answer to your question is: Use what works for you. But this might as well include that you know if that really works for you instead of beeing something that you stumble over and hope it will work (because it seems to work for so many others) I used PHP for years, I actually used Perl before PHP but got tired of the Perl oddness. I moved on to Python and love it. There are things in it I don't like (just see subprocess) but for the most part, its gorgeous. Yeah, I used C (for the web), i tried perl and came to python. Whenever I checked PHP I found it so bad designed (if at all) that it really hurted. And occassionally I'm asked for help on PHP questions so I see nothing essentially has changed on the matters for the last 10 years. Its still confusing naming of functions (hello namespaces), not really a type system (think '1' + 2 ) and the like. PHP5 didn't change much because if you want to adopt OOP you could as well just use a language which does this for years (even Ecmascript) or - as most seem to do - just continue to code old style. This horrible mixing of code and HTML is even found in JSP code these days. T. smime.p7s Description: S/MIME Cryptographic Signature
Re: [GENERAL] php + postgresql
Leif B. Kristensen wrote: On Friday 25. July 2008, Christophe wrote: ... My 2 cents: The prime reason for the popularity of PHP is probably the very gentle learning curve. You can start with a static HTML page, and introduce a few PHP snippets to show dynamic content. For us self-taught people, that means that you get instant results with minimal work. Seems you never used a decent template engine, such as TAL http://www.owlfish.com/software/simpleTAL/tal-guide.html Which really is code by example instead of intermixing language constructs with HTML which is incredibly hard to maintain. If any language want to compete with PHP in popularity, I believe that it must be just as easy to mingle with HTML. $DEITY, I would love to be able to include Perl code in a HTML page inside a pair of ?pl and ? tags. Most if not all other languages which are used for the web do have those ways, which does not mean its recommended to do so. Now, I don't write PHP scripts like that anymore. I like to have every single character served as HTML to be generated by a function. And I Which is for sure very performant ;) realize that Perl would do that even better than PHP. But as I have become quite proficient with PHP, I tend to keep using that. It surely does the job. And hope that you arent bitten by nasty bugs in the language implementation or your security configuration of it :-) Ok, enough PHP bashing. Sun is shining here and so I invite everybody to enjoy the weekend :-) T. smime.p7s Description: S/MIME Cryptographic Signature
Re: [GENERAL] php + postgresql
Bill Wordsworth wrote: ... PHP is faster than Python, has a smaller memory foot-print than Python, has better SOAP features than Python, and is better suited for the web than Python. Python is better suited for the cli/mac/desktop/phone. Do you have proof for that? Or is this similar to MySQL is faster then Postgresql? I see a different picture: http://shootout.alioth.debian.org/debian/benchmark.php?test=alllang=pythonlang2=php And nobody made Engineers the boss of us. We also can't compare Database v Language, that MySQL = PHP where PostgreSQL = Language of Your Choice. We can like PHP *and* PostgreSQL and stand up for both. Cheers, Bill Oh, we can do that exactly the same way as someone can stand up for a ... err.. whatever language ;-) T. smime.p7s Description: S/MIME Cryptographic Signature
Re: [GENERAL] php + postgresql
Hi, admin wrote: Thanks again for replies. I know those questions were pretty vague. I need to set up some methodical test scripts that replicate my problems, so that it is clear what is going on. There does seem to be some evidence of problems historically with PHP and persistent connections in PostgreSQL, on the PHP forums. The advice is typically to avoid them. usually it goes so far to avoid PHP alltogether ;) Is there any special reason to use PHP? There are a couple other scripting languages useable for the web which do all have better abstration available. (afaic even PHP does have some more abstration to just using pg* functions) Tino. smime.p7s Description: S/MIME Cryptographic Signature
Re: [GENERAL] php + postgresql
Aarni Ruuhimäki wrote: ... Not sure what causes this with your server but I always use something like this, ie first connect then do your stuff and then close the connection: require(dbconnect.inc); // holds the $conn which is pg_connect(with passes) I would avoid that in favour of using $HOME/.pgpass http://www.postgresql.org/docs/8.1/interactive/libpq-pgpass.html HTH Tino smime.p7s Description: S/MIME Cryptographic Signature
Re: [GENERAL] php + postgresql
Hi, admin wrote: Is there any special reason to use PHP? There are a couple other scripting languages useable for the web which do all have better abstration available. (afaic even PHP does have some more abstration to just using pg* functions) Well, yes, there are alternatives of course and I could write this stuff in perl or python but it'd take me 10 times as long because my experience is elsewhere. Learning new stuff is always good, but at the end of the day I get paid for making stuff work on time and in budget ... mostly :-) I think that PHP (like PostgreSQL, perhaps?) suffers from a reputation hangover from years ago. PostgreSQL was supposedly slow, PHP is supposedly undisciplined and unprofessional. You sure can still Well no PHP is conceptual undisciplined and confusing. I would not compare this with Postgresql itself which is very professional developed with a great vision. PHP is just and always was a hack. write spaghetti with PHP5 if you want to, but you can also write decent code with planning and standards. But good, bad or ugly, it's what I personally am most productive in. I have used PHP's PEAR DB abstraction class many times. It doen't really save much time or effort writing code, and has a performance overhead. I don't need to allow the possibility of switching to another database and stuff like that. Sure, you must consider it yourself but having a little abstraction helps even as kind of inherent documentation when you later need to touch your code again. Regards Tino smime.p7s Description: S/MIME Cryptographic Signature
Re: [GENERAL] php + postgresql
Andrew Sullivan wrote: On Fri, Jul 25, 2008 at 01:41:50PM -0400, Jonathan Bond-Caron wrote: Uhm, let's not start a PHP debate. Well it was just a innocent question since the original poster did not seem to know the language of choice good enough to solve this rather basic problem. (Note, however, that I'm firmly in the camp that says you can write lousy code in any language.) Sure, but it seems some languages makes it more easy to write lousy code instead of something elegant. (And be it just because they are so common that you just have a bay of bad examples to choose from, add some cargo cult programming and be ready :-) Ok, back on topic again :-) T. smime.p7s Description: S/MIME Cryptographic Signature
Re: [GENERAL] A couple of newbie questions ...
Shane Ambler wrote:
Raymond O'Donnell wrote:
...
INSERT INTO table (fld_y, fld_z) VALUES ('y', 'z');
Another way is INSERT INTO table VALUES (NULL,'y','z')
of course you meant:
INSERT INTO table VALUES (DEFAULT,'y','z')
since Null would be wrongly insert NULL value instead
of using the sequence value.
Regards
Tino
smime.p7s
Description: S/MIME Cryptographic Signature
Re: [GENERAL] Getting source code for database objects
Richard Huxton wrote: Artacus wrote: Easier would be just uing pg_dump -s schema.sql to get all schema objects so you could check them into subversion. If you want only specific objects, pg_dump -l listofobjects, then edit this list as you like and use pg_dump -L listofobjects someobjects.sql The -l and -L options are not recognized on my server 8.3. I can use -t to iterate thru each table, but I don't see a way to do one function at a time. They're part of pg_restore, not pg_dump. You need to use -F c with the pg_dump to let pg_restore generate lists in this way. Ah yes, correct. Thats how it is when you write this from memory instead of trying it ;-) I'm always operating on the pg_dump -F c file for consistency. T. smime.p7s Description: S/MIME Cryptographic Signature
Re: [GENERAL] Getting source code for database objects
Artacus wrote: On pgadmin, when you click on a table or function, you get the source code (DDL) to create that table or function. I want to take that and check it into subversion so I have one file for each table, function, view, etc. My question is, how do you get that source code? I've been playing with pgadmin and wireshark trying to figure out what commands or queries it is using to no avail. Easier would be just uing pg_dump -s schema.sql to get all schema objects so you could check them into subversion. If you want only specific objects, pg_dump -l listofobjects, then edit this list as you like and use pg_dump -L listofobjects someobjects.sql This should get you going. Cheers Tino smime.p7s Description: S/MIME Cryptographic Signature
Re: [GENERAL] To store and retrive image data in postgresql
Hi, Craig Ringer wrote: Brent Wood wrote: ... I need to store an image in postgresql database and after that i need to retrive the image back.Can you please help me how to do this? ... - Storing image data in the DB is probably much less efficient in storage space and for access; and - You face possible MVCC bloat with image data when storing in the DB, so careful management would be needed The first really cool MS-SQL feature I've heard about relates to this. NTFS now has transaction support. I'm pretty sure I remember hearing *snip* In that case, would that MVCC bloat not just transferred to the filesystem? Large objects should currently give you pretty much the same results without plug for MS-SQL :-) And in most cases bytea as mentioned elsewhere are not so bad either (especially for p*m-Images :-) Cheers Tino smime.p7s Description: S/MIME Cryptographic Signature
Re: [GENERAL] limits?
Thomas Kellerer wrote: Steve Atkins wrote on 23.06.2008 20:21: In real use you're unlikely to hit any limits, theoretical or practical, I imagine that the 1GB column-value limit is something that could be reached though. Especially for BLOB (aka bytea) or CLOB (aka text) columns. No, since they are stored out of band (toast tables). For *lob there is a lob type which stored the data completely separate, not to be confused with bytea and text (varchar). Tino. smime.p7s Description: S/MIME Cryptographic Signature
Re: [GENERAL] Method to detect certain characters in column?
Hi Ian, Ian Meyer wrote: Ah, so I forgot to mention the one caveat to this (sorry!) was there was a ton of punctuation/spaces and other ilk.. so this is what I came up with: bco=# select name from member where not (name ~ '^[A-Za-z0-9[:punct:] ]*$'); name -- Señorita Lolita Long Pig täkäurgh blåbärsöl fuchér MkII fuchér ver2.0 Gûm-ishi Ashi Gurum kängnäve Fuchér-version 2.1 fuchÃ(c)r Uh, is that really the name as it should be? To me it looks much more like UTF-8 stored in SQL-Ascii. Maybe converting it correctly would help? Cheers Tino smime.p7s Description: S/MIME Cryptographic Signature
Re: [GENERAL] Losing data
Hi, Garry Saddington wrote: On Thursday 19 June 2008 16:55, Joshua D. Drake wrote: On Thu, 2008-06-19 at 16:55 +0100, Garry Saddington wrote: ... Yes I thought of this but once the report is sent to the DB a separate query is run to get all of that teacher's reports and these are then displayed on a new page. They all appear here but then disappear later. Zope has transaction machinery that rolls everything back on an error, so Postgres must have are you running the report within the same request that made the write? In this case you would be in the same transaction (and see uncommitted data) if then an exeption is raised after you display the report the transaction would be rolled back. You should check your zope logs. Regards Tino smime.p7s Description: S/MIME Cryptographic Signature
Re: [GENERAL] How to INSERT empty line into SEQUENTIAL table from PHP
A. Kretschmer wrote: insert into table_xx (id, field2, ...) values (NULL, ...) I'd say it should be DEFAULT instead of NULL :-) T. smime.p7s Description: S/MIME Cryptographic Signature
Re: [GENERAL] fitler database list
Christophe Simonis wrote: Is it a way to filter the database list on the tables containt in the database ? Yes it is. Cheers Tino :-) smime.p7s Description: S/MIME Cryptographic Signature
Re: [GENERAL] PL/pgSQL graph enumeration function hangs
Charles F. Munat wrote: Thanks, but the join clause is there, it's just buried in the subqueries. If there is a problem, it is probably that the loop never ends. Or it could be that the answer is exponential, and I just have too many rows in the source table and too deep a graph. I figured out how to do it in the application with one call to the database and a simple recursive method in a class, though, so I'm not going to use a stored function in the DB. If you have figured it out this way you can even use one of the PL/languages to implement it within the database :-) Cheers Tino smime.p7s Description: S/MIME Cryptographic Signature
Re: [GENERAL] Extracting data from deprecated MONEY fields
Hi, Shane Ambler wrote: ... I think the steps Joshua is referring to are - 1. pg_dump -t mytable_with_money mydb mytable_backup.sql 2. edit table definition in backup file to use numeric 3. remove $ and , from money column data 4. DROP TABLE mytable_with_money 5. psql mytable_backup.sql While the data is in a text file regex tasks to remove the money formatting become a lot simpler. to_char() and back to numeric shouldn't be a problem within the database and we have regex too if anything fails. I don't think you need to dump and edit the dump to achive that. Regards Tino smime.p7s Description: S/MIME Cryptographic Signature
Re: [GENERAL] Extracting data from deprecated MONEY fields
Hi, Tino Wildenhain wrote: Hi, Shane Ambler wrote: ... I think the steps Joshua is referring to are - 1. pg_dump -t mytable_with_money mydb mytable_backup.sql 2. edit table definition in backup file to use numeric 3. remove $ and , from money column data 4. DROP TABLE mytable_with_money 5. psql mytable_backup.sql While the data is in a text file regex tasks to remove the money formatting become a lot simpler. to_char() and back to numeric shouldn't be a problem within the database and we have regex too if anything fails. I don't think you need to dump and edit the dump to achive that. Ah sorry forget that... neither to_char nor cast to text works. Really a horrible datatype :( Tino smime.p7s Description: S/MIME Cryptographic Signature
Re: [GENERAL] How can I compare sql create script with running database?
Hi, Bjørn T Johansen wrote: On Thu, 05 Jun 2008 11:06:36 +0100 Raymond O'Donnell [EMAIL PROTECTED] wrote: On 05/06/2008 10:52, Bjørn T Johansen wrote: If I already have a running database, how can I compare the tables in the database with the sql script to discover the differences? You can use pg_dump with the -s option to dump the schema of the database, and run it through the diff tool of your choice. Ray. Well, not really an option because I don't think the dump will be an exact match to the sql script Unless you create all your objects with your script into a different Database on the server then use pg_dump -s on both and compare the result :-) Cheers Tino smime.p7s Description: S/MIME Cryptographic Signature
Re: [GENERAL] Temporary Tables and Web Application
Hi, Tim Tassonis wrote: Hi all I assume this is not an uncommon problem, but so far, I haven't been able to find a good answer to it. I've got a table that holds log entries and fills up very fast during the day, it gets approx. 25 million rows per day. I'm now building a web application using apache/mod_php where you can query the database and then should be able to page through the results. you should be aware that PHP isnt the only scripting language with an apache module and not neccessary the best choice among them. My idea was that whenever a user constructs a query, I create a temporary table holding the results and then page through this table, which should work very well in principle. That means you are more or less constructing materialized views :-) But if you hold the session anyway, then see below. But from what I've been able to find out, temporary tables live only in the Postgres Session they have been created in and are destroyed upon session descructuion. Now, with apache/php in a mpm environment, I have no guarantee that a user will get the same postgresql session for a subsequent request, thus he will not see the temporary table. Thats the problem and if you have failover/loadbalancing situations, even more so. Is there a way to create temporary tables in another way, so they are visible between sessions, or do I need to create real tables for my purpose? And is the perfomance penalty big for real tables, as they have been written to disk/read from disk? To start with, you should avoid reconnecting to the database for every request. Not only because of loosing the session context but also to avoid connection overhead. Usually this is done by connection pooling. You can then try to trac user:connection relationship as much as possible thru the connection pool. If you have that, there is actually no need for the temp tables. Instead you can just use a regular cursor and scroll it as neccessary. Almost all frameworks should give you reasonable pool implementations, some additional memory caching on top of it and there are also a lot of other methods to help you with that, for example pgpool and pgbouncer. Regards Tino smime.p7s Description: S/MIME Cryptographic Signature
Re: [GENERAL] Defining character sets for indicidual fields
Hi, Steve Atkins wrote: On May 31, 2008, at 6:22 PM, Ram Ravichandran wrote: Hi, By default, my postgresql server is set to use UTF8 character set. I was wondering if there is any way to make sure that certain fields like url etc. only makes use of ascii. My main aim is to save space by using only 1 byte / character for urls (some of the urls are over 200 characters long). Is this possible? Or are all characters eventually converted to UTF8 during storage? An ascii string and the UTF8 representation of it will take exactly the same number of bytes, so if space used is your concern it's not an issue. Even more, if you convert URLs from urlencoding to clear text, you can quickly leave the ASCII char range (think punicode for the fqdn, think utf-8 for the path) Cheers Tino smime.p7s Description: S/MIME Cryptographic Signature
Re: [GENERAL] cannot use result of (insert .. returning)
dvs wrote: Hello, I need to use query like: ,c from anytable where condition but it say ERROR: syntax error at or near into did you try with: select (insert into test (a) values (x) returning b) query_a JOIN c ON ... ? Tino. smime.p7s Description: S/MIME Cryptographic Signature
Re: [GENERAL] Extracting \ Generate DDL for existing object permissions
Raymond O'Donnell wrote: On 22/05/2008 22:33, smiley2211 wrote: 1) dump database A - 2) Extract permissions from database B - (HOW) How about dumping B using the plain-text format, then running the output through grep, looking for lines starting with GRANT...? Hint: sometimes easier is to run pg_dump -fc and then pg_restore -l to create an object list and operate your filters on this list, then use the resulting list with pg_restore -L With GRANTS, which are one-liners, it works either way. But the general approach works very well with any statement, think of multiline create table, create function ... Cheers Tino smime.p7s Description: S/MIME Cryptographic Signature
Re: [GENERAL] Substring Problem
[EMAIL PROTECTED] wrote: Type casting is required since 8.3, try SELECT substring(date :: varchar from 1 for 7) AS stryearmonth, ^^ sorry but this hurts and should not recommended. I think depesz approach with to_string() and the correct format string is the better solution. (think of datetyle oddities) - one of the reasons I believe most of the implicit casts have gone ayway. COUNT(id) AS countofnumber FROM (SELECT * from downloads ) AS foo GROUP BY stryearmonth ORDER BY stryearmonth ASC btw, whats the reason for the subselect? T. smime.p7s Description: S/MIME Cryptographic Signature
Re: [GENERAL] PHP Warning: pg_connect(): Unable to connect to PostgreSQL server: could not connect to server:
Hi, Abdus Samad Ansari wrote: PHP Warning: pg_connect(): Unable to connect to PostgreSQL server: could not connect to server: I have setup PHP/Postgres and is running fine upto document root i.e. /var/www/html, but when i am calling it through a cgi-bin php file it is giving log error as : [error] [client 127.0.0.1] PHP Warning: pg_connect(): Unable to connect to PostgreSQL server: could not connect to server: \x04\xe6\xe3\xbfPF; well there are a couple of checks you could do to find out: 1) decide how to connect - via IP or via unix socket 2) in each of the above see if postmaster is indeed listening to that socket (netstat is your friend) - adjust postgresql.conf and/or start options accordingly. depending on your distribution you might want to enable logging 3) try connection via psql command line client - see if you have matching pg_hba.conf entries 4) try to connect via psql command line client with the same user your script runs (apache in your case) - same as (3) but ident vs. md5 (password) in pg_hba.conf after all you might want to use some pooling layer because pg_connect from cgi can get very expensive (also note that outside of PHP there are a lot possible elegant solutions to that) Tino smime.p7s Description: S/MIME Cryptographic Signature
Re: [GENERAL] Import German Decimal Numbers
Jan Christian Dittmer wrote: Thank you very much! You have remind me that the our server runs under Linux and not under Windows as our clients :-) So indeed I can use a sed-pipe construct to switch '.' and ','. But wait, there is just another problem then. Our date format is also german :-( DD.MM.YY or DD.MM.. So if I just exchange '.' and ',' the date will be unreadable for the import :-( The (current) file is 1.4 GB so it will take ages to let awk chew on it I guess. If all fails you could copy into a another table where you use text columns and then translate afterwards via to_date() and to_number() / replace(). Cheers Tino smime.p7s Description: S/MIME Cryptographic Signature
Re: [GENERAL] Import German Decimal Numbers
Thomas Pundt wrote: On Mittwoch, 7. Mai 2008, Jan Christian Dittmer wrote: | The (current) file is 1.4 GB so it will take ages to let awk chew on it | I guess. If you think awk is a bottleneck, I'd recommend using perl instead. It's waaay faster and should process your file within minutes if not faster. isnt perl just another frontend for awk? ;)) SCNR ;) There is also http://pgfoundry.org/projects/pgloader/ and if not already implemented it should be fairly easy to implement a data filter within this one. Cheers Tino smime.p7s Description: S/MIME Cryptographic Signature
Re: [GENERAL] Import German Decimal Numbers
William Temperley wrote: ... Bit of a tangent, but Is there any possibility of SQL injection via data provided to copy? depends on how you call COPY, but usually not :-) Cheers Tino smime.p7s Description: S/MIME Cryptographic Signature
Re: [GENERAL] Request for Materialized Views
Hi, [EMAIL PROTECTED] wrote: Hi, I am not sure about the right place to post this question so I'm trying here first: We are using Postgres in our company successfully since 2001 but now we arrived at the conclusion that we really need Materialized Views for our further business. Also we decided that we should try to pay someone from the community for the feature to be implemented for Postgres instead of doing it ourselves. While we know that this is common practice in open source projects I would like to ask how such a kind of announcement should be made for Postgres. Short of having an all purpose macro, did you try one of the already available methods to roll it your own? Otoh, I do not want to spoil the fun of putting yes we have materialized views to the list of marketing relevant things ;-) Cheers Tino smime.p7s Description: S/MIME Cryptographic Signature
Re: [GENERAL] operator varchar = integer
Hi, Daniel Schuchardt wrote: David Fetter schrieb: That technical debt is a risk to your whole project, and you need to dedicate resources to paying it down. http://en.wikipedia.org/wiki/Technical_debt There are ways to get those automated casts, but they will only make your situation worse in the long run. Cheers, David. *g* interesting standpoint and your right but: it is impossible for us to find all the points where the new 8.3 behavoir would crash at the first time. so our next versions would be very buggy and our customers wouldn't be happy ;-) the next problem is that our service personal has to be traineed too; they dont know much about casting, 81 does it automatically; problems problems problems. if it is not possible (i know it is) ;-) to recreate automatic casts in 83 we would not be able to upgrade to 83 the next years. the next possible date would be in about 3-4 years with the next major release. PS: our db has about 500 functions, 300 tables, 1000 indexes, 1200 Views that all use implicit casting. and: everything is working fine ;-) :-P so we have to choose another way. Well err... implicit table joining is also off per default I believe. So if you had used it a lot you would have a similar problem. Comparing int with text in general does not sound like a very good idea to me. It should be quite easy to write a script to identify such places so you can either change the datatypes (preferred) or add the cast. Then rerun your automated regression tests... Cheers T. smime.p7s Description: S/MIME Cryptographic Signature
Re: [GENERAL] operator varchar = integer
Hi, Daniel Schuchardt wrote: Tino Wildenhain schrieb: Hi, ... Comparing int with text in general does not sound like a very good idea to me. It should be quite easy to write a script to identify such places so you can either change the datatypes (preferred) or add the cast. Then rerun your automated regression tests... Cheers T. it is not possible to do this. we also used automatic casting as a feature: if a user starts a search, we never check the data type he puts in. in 81 postgresql automatically casts the user input in dependece of the column. so all our search masks has to be checked to, edit fields need to get a data type and so on. example: user types 1 - our application automatically starts a search LIKE '1%' user types 12 - our application automatically starts a search LIKE '12%' and so on. and it doenst matter if the searchfield is char or integer, 81 does it automatically. if it is a good or bad thing to do it is another question. it works in 81 and so we used this feature. hum. (yes still think there is a lot of improvement possible) but you could either change the schema to use text instead of int or easily identify the places where LIKE is used and cast the left hand expression explicitely to text. When I think about it you should actually know for sure which places they are since you probably created functional indexes? Also it sounds like you are assembling SQL for queries? In this case its even more easy to put the casts in place (or change the column datatype). You mentioned support people - do they write raw SQL? T. smime.p7s Description: S/MIME Cryptographic Signature
Re: [GENERAL] operator is not unique: integer || integer
Hi, Daniel Schuchardt wrote: ... in 81: postgres=# SELECT 1::INTEGER||1::INTEGER; ?column? -- 11 (1 row) *shudder* is this actually a port of an application originally targeted at M*Sql? ;) Are you using those columns somewhere with their real type - as integer? I mean if you use them as text everywhere why not change the type once? T. smime.p7s Description: S/MIME Cryptographic Signature
Re: [GENERAL] operator is not unique: integer || integer
Daniel Schuchardt wrote: Tino Wildenhain schrieb: Hi, Daniel Schuchardt wrote: ... in 81: postgres=# SELECT 1::INTEGER||1::INTEGER; ?column? -- 11 (1 row) *shudder* is this actually a port of an application originally targeted at M*Sql? ;) Are you using those columns somewhere with their real type - as integer? I mean if you use them as text everywhere why not change the type once? T. *g* yes, sure we have to CAST it now. thats no problem. but the problem is to find all the places where to cast. and you see that there are many possiblilitys. another example?: RAISE NOTICE error during validation % :, 'ks:'||ks||'@'||loopdate||''; (here LoopDate is a DateTime) another one: here we need to add 4 CASTS. you see. CREATE OR REPLACE FUNCTION date_to_yearmonth_dec(TIMESTAMP) RETURNS INTEGER AS $$ DECLARE R INTEGER; BEGIN IF extract(month FROM $1)11 THEN R:=extract(year FROM $1)||0||extract(month FROM $1)-1; ELSE R:=extract(year FROM $1)||extract(month FROM $1)-1; END IF; RETURN R; END$$LANGUAGE plpgsql IMMUTABLE; RETURN extract(year FROM $1)*100+extract(month FROM $1)-1; was too clean and easy? ;)) Looks like a good oportunity to clean up your code before anything unexpected happens :-) Cheers T. smime.p7s Description: S/MIME Cryptographic Signature
Re: [GENERAL] How to modify ENUM datatypes?
Andrew Sullivan wrote: On Fri, May 02, 2008 at 09:18:39AM -0700, Rob Wultsch wrote: Not really Postgres's problem, but for whatever its worth if I do the following on Debian stable: $apt-get install postgresql I get 7.4 . When I install Debian I generally expect the software to be supported for a long time. Perhaps it might make sense to declare it dead except for security issues? ... (I confess I am a little astonished that the most recent stable release ended up with 7.4. I thought that at _least_ 8.0 was out prior to freeze. What happened?) apt-get install postgresql-8.1 gives you 8.1 in etch (stable) in addition to 7.4. Cheers Tino smime.p7s Description: S/MIME Cryptographic Signature
Re: [GENERAL] How to modify ENUM datatypes?
Hi, Merlin Moncure wrote: On Thu, Apr 24, 2008 at 3:01 PM, Tino Wildenhain [EMAIL PROTECTED] wrote: Merlin Moncure wrote: I think you're being a little too hard on enums here. I was actually in the anti-enum camp until it was demonstrated to me (and in my own testing) that using enum for natural ordering vs. fielding the ordering of the type out to a join is can be a huge win in such cases where it is important. Relational theory is all well and good, but in practical terms things like record size, index size, and query performance are important. Uhm. Sorry what? Can you demonstrate this particular use? When I first saw discussion about enumns I kinda hoped they will be implemented as kind of macro to really map to a table. But here you go. I'm still looking for a good example to demonstrate the usefullness of enums (same for arrays for that matter) You must not be aware that enums are naturally ordered to make that statement. Suppose your application needs to order a large table by a,b,c where b is the an 'enum' type of data. With an enum, the order is inlined into the key order, otherwise it's out of line, meaning your you key is larger (enum is 4 bytes, varchar is guaranteed to be larger), and you need to join out to get the ordering position, use a functional index, or cache it in the main table. I see, but couldn't you just use int in this case? And map only when you need the values for display (usually you want it localized anyway) I agree with disagree with you on arrays. I think they are generally a bad idea in terms of using them as a column type. However they are useful passing data to/from functions and back/forth from the client. Yes of course, I thought of that (wondering why we can't use value expressions everywhere) Tino -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] How to modify ENUM datatypes?
D. Dante Lorenso wrote: Alvaro Herrera wrote: D. Dante Lorenso wrote: Or, here's another way to look at it ... make it easier to modify ENUM datatypes because we all know that you will eventually need that feature whether you males, females, and unknowns think so or not. Agreed. Let's keep in mind that the current ENUM implementation is completely new. Here's a use case that I doubt too many are aware of ... what's unique about ENUM is that the data for the ENUM becomes part of the schema of a database. This is actually something I like very much. I have systems where the DEV and PROD databases are separate and must be synchronized when pushing out new features. I currently use non-free tools to synchronize just the schemas for both databases. Often if a new row is added to a lookup table, that row doesn't make it to the QA or PROD databases because it's part of the data of a table and is not part of the schema. For data (like ENUM data) that should be consistent across databases, it helps if it gets deployed with the schema so that lookups will succeed properly. Well since its configuration and not payload its nothing wrong with just having the data in your repository as well and load it every time when you roll out a new release. Cheers Tino -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] How to modify ENUM datatypes?
Merlin Moncure wrote: On Thu, Apr 24, 2008 at 12:22 PM, Andrew Sullivan [EMAIL PROTECTED] wrote: The first time I encountered them, I thought enums were a filthy, ill-conceived answer to a problem that didn't exist, implemented by people who didn't understand relational databases. With considerably more experience under my belt than then, I say now that my original estimation was too kind. I think you're being a little too hard on enums here. I was actually in the anti-enum camp until it was demonstrated to me (and in my own testing) that using enum for natural ordering vs. fielding the ordering of the type out to a join is can be a huge win in such cases where it is important. Relational theory is all well and good, but in practical terms things like record size, index size, and query performance are important. Uhm. Sorry what? Can you demonstrate this particular use? When I first saw discussion about enumns I kinda hoped they will be implemented as kind of macro to really map to a table. But here you go. I'm still looking for a good example to demonstrate the usefullness of enums (same for arrays for that matter) Cheers Tino -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Secure where in(a,b,c) clause.
Steve Atkins wrote:
...
I count the number of values that I want to put in the IN () clause,
then create a query string with the right number of bind variables
in the in clause, then bind the values.
So for {1, 3, 5} I'd use select * from foo where bar in (?, ?, ?) and for
{1,5,7,9,11} I'd use select * from foo where bar in (?, ?, ?, ?, ?)
Then, in perl-speak, I prepare that string into a query, loop through
all my values and bind them one by one, then execute the query.
You mean something like:
items=(1,2,5,6,9)
cursor.execute(SELECT ... FROM foo where bar in (%s) %
','.join('?'*len(items)),items)
? :-)
Oh.. I forgot he said PHP...
SCNR
Tino
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Survey: renaming/removing script binaries (createdb, createuser...)
Chris Browne wrote: ... pg_ctl is really more like the scripts in /etc/init.d; whatever it ought to be called instead, I don't think safe_postgresqld is it... eek. where is that save_ something coming from? Apache uses apachectl which seems pretty forward - pg_ctl seems to be in the same spirit. Naming it pgctl could simplify typing and making it a wrapper (similar to zopectl :-) with start/stop/status/debug/run ... could have some benefit. Regards Tino -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Unicode comment on Postgres vs Sql Server
Swaminathan Saikumar wrote: I didn't have proper knowledge about the UTF8 format, thanks. I originally meant nvarchar nchar, which is basically varchar char that supports Unicode regardless of the database encoding. Well, we don't need that when we have UTF8. There could be edge cases speed wise when you use UCS16 or UCS32 internally but I'm not sure how well this would justify a new datatype. The current problem isnt so much with encoding database wise, its more about collating database cluster wise - which is something not easily solved when you want to do it according to the SQL spec. You could work around that with a functional index. Regards Tino Wildenhain ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Unicode comment on Postgres vs Sql Server
Swaminathan Saikumar wrote: I am familiar with MS Sql Server just started using Postgres. For storing Unicode, Sql Server uses nvarchar/char for unicode, and uses char/varchar for ASCII. Postgres has this encoding setting at the database level. I am using UTF8 Unicode for most of my data, but there is some data that I know for sure will be ASCII. However, this is also stored as UTF8, using up more space. This is wrong - ASCII is a subset of UTF8 and therefore uses exactly one byte for every ASCII char. See http://en.wikipedia.org/wiki/UTF-8 for example. At first sight, it looks like the the more granular level design is better. Any comments? If you agree, does it make sense to add this as a new datatype to Postgres? Which new datatype? Regards Tino ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Querying the schema for column widths - what syntax do I use?
Hi Howard, Howard Wilkinson wrote: I am working on some upgrades to the MyDNS open source product. I have some expertise in MySQL but am not overly familiar with PostgreSQL and need some guidance on how to query the schema for the maximum size of data a column can hold. In MySQL I can do either: a DESCRIBE command or do SELECT COLUMNS command. And then parse the result for the length in the type column of the row returned. How would I do a similar function using PostgreSQL - I have tried to find this in the manuals and in this mailing list but not found any pointers to get me started. Apologies for asking such a simple question but I am being a bit lazy as I want to get on with releasing the MyDNS code. beside the correct answers you got relating the informational_schema, since I do not know what MyDNS is and what you are doing with the maximum size of the column, are you aware that postgresql bails out if you put in a string which exceeds the column size (so you can just try rather then check beforehand if thats what you do) or you also get the description in the cursor when you do the select on a table. Also, text type could be used to hold potentially large strings without harm (so if the string is short, its no difference but you can easily go up to over a gig) Regards Tino ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] selective backup and restore
Hi, [EMAIL PROTECTED] wrote: Hi All. Is it possible in postgreSQL 8.2.4, using pg_dump.exe, make a backup/restore conditioned to the key of some tables (respecting existing constraints)? You can either use the -t option or with a full backup in custom format you can pick a few objects (tables, views, ...) by creating the list with pg_restore -l and edit it in a text editor and then use -L to extract the DDL/DML for these objects. See: http://www.postgresql.org/docs/8.3/static/app-pgdump.html -t table --table=table for pg_dump and http://www.postgresql.org/docs/8.3/static/app-pgrestore.html -F format --format=format (use t or c) and -l --list List the contents of the archive. The output of this operation can be used with the -L option to restrict and reorder the items that are restored. -L list-file --use-list=list-file Restore elements in list-file only, and in the order they appear in the file. Lines can be moved and can also be commented out by placing a ; at the start of the line. (See below for examples.) Regards Tino ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Regex query not using index
Postgres User wrote: im trying to allow the client to pass a varchar param into my function, and want to avoid any parsing of the parameter inside the function, or code to build a sql string. if the function can use this code, it will be compiled and optimized (unlike a dynamic sql stirng) select * from mytable where fielda ~ p_param No, you should never let users specify raw regex. at best they can hog down your server. Regex is a state engine and you can create endless loops. Maybe we can see the overall picture of your query? Regards Tino ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Perceived weaknesses of postgres
Csaba Nagy wrote: http://www.theserverside.com/news/thread.tss?thread_id=48339 The interesting part is where somebody asks why NOT use postgres, and it's answers could give some additional hints to those interested on what people find missing from postgres to adopt it. Just to summarize some of the answers: * major PITA to upgrade between major versions; I wonder which other databases (also commercial) can do that. * executing a single query on multiple cpus/cores; * no direct table cache control; * accent-insensitive text comparisons; multiple collations would be nice... just accent-insensitive would not make much sense imho. * fast select count(*); SELECT count(*) WHERE False; is very fast for me ;) Greets :-) Tino ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] dynamic crosstab
Hi, SunWuKung wrote: Hi, I found this to create dynamic crosstabs (where the resulting columns ... This could work although for hundreds of columns it looks a bit scary for me. Well I'd say hundreds of columns are always scary, no matter how you do it :-) ... I know that most db people don't care much about pivot/crosstab in the db but imagine this situation: I am storing questionnaire results on people. Since the questionnaires are created by users I have no other way than using an EAV model like personID, questionID, responseValue to store responses. Now this table gets long 300 question per questionnaire, 3000 people and we have 1m row. Now whenever I need to download this data in my case 2/3rd of it would be redundant if I could pivot it first - and in a 20MB csv its significant (I know its a tradeoff between processing and storage). Moreover my users can't do anything with this dataformat - they need to pivot it offline anyway, which is not easy (Excel cant do it, Access cant do it, numberGo cant do it for different reasons). What about not pivoting it? You can run your analysis directly against your database. Although the application could do it I think this is a generic functionality that the database is more suited for. Well after all you want a CSV not a table. You could shortcut this with a generic query which creates array out of your columns and join them to a CSV line. This would just be outputted as one single column from database. Please let me know if you know of a good db based way to create a dynamic crosstab in Postgres - or why there shouldn't be one. See above :-) Regards Tino ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] A select DISTINCT query?
Phil Rhoades wrote: People, I want to select from a table ONLY unique records ie if a column has values: 1 2 3 3 4 5 I want ONLY these records returned: 1 2 4 5 SELECT count(*) as cnt,a,b,c FORM yourtable GROUP BY a,b,c HAVING cnt=1 should do. Regards Tino ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Query to get column-names in table via PG tables?
Ken Johanson wrote: I am looking for expertise on how to program the equivalent to this query, but using the pg_catalog tables, which I understand have fewer security restrictions than information_schema in some cases: SELECT column_name FROM information_schema.columns WHERE table_catalog=? AND table_schema=? AND table_name=? ORDER BY ordinal_position Do what psql does...launch it with psql -E, and it will echo any internal queries it makes back to you. Do \d on a couple of tables and you should see what is going on. The output of this is very verbose and broken into multiple queries making joins difficult for me to understand, I'm afraid; my current experience level likely will not reliably produce a single-query equivalent to the above. I have to again ask for designer expertise on this one. Also a factor is that since the query will be hard coded into a driver, knowledge of how to make it most durable across server versions would be a benefit (assuming the underlying tables change?). Ah driver you say? For which language? Will it be coded in C? If you want to do it most reliable and do not want to code for every PG version and also do not want to use information_schema (why btw?) you can also resort to just SELECT * FROM schema.table WHERE false; and then inspect the cursor for column names and datatypes. Regards Tino ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] insert into ... select ... and column order
Tore Halset wrote: Hello. One of our users tried a insert into ... select ... that gave a strange error message. After digging into the issue, the problem seem to be that the order of the columns in the select statement must match the table definition. Here is a way to reproduce this case. ... Why does the column order matter when the subselect has all the correct column names? When I noticed this long ago I just blamed it to SQL standards ;) I do admit I never checked with the documentation. And yes the names of the columns do not matter, just use the correct order - either default or better: INSERT INTO ... ( a,b,c ) SELECT a,b,c FROM since new columns or reorder would not disturb your insert. Regards Tino ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Simpler dump?
Hi Ted, Ted Byers wrote: Thanks Uwe This is a great start. It reduces the dump from 2 MB down to 167K, but out of 6833 lines of SQL, 5744 relate to the public schema in the DB, and I didn't touch that. It has over a dozen types, 419 functions, c., that were put there by postgresql the moment I created the database. I'd expect the same stuff to be there the moment I issue the create database directive on the host machine, so all I really want is the dozen sequences, two dozen tables, and the suite of constraints I created, all in the schema specific to my new DB. Well thats usually not the case unless you changed the default database per accident. You can hope but not be sure to find the same situation on your server. Is there a reason pg_dump dumps the stuff in public even though that stuff seems to be created, and therefore present, in every database I create on a given server instance? Isn't that duplication a waste of space, and it's presence in the dump a waste of CPU cycles? Well, at the moment you seem to waste CPU cycles, network bandwith and storage on the mailinglist server by not just looking at the manual of pg_dump, which has for example goodies as: -n schema --schema=schema Dump only schemas matching schema; this selects both the ... HTH ;) Tino ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Simpler dump?
Hi Ted, Ted Byers wrote: --- Tom Lane [EMAIL PROTECTED] wrote: ... it's not pg_dump's fault --- you need to clean out template1. Thanks Tom, Where will I find template1? When I look at the databases on the server, the only template I see is called template_postgis. Most of the extra stuff I see in all my databases relates to geometry that I find in this template. When I installed postgresql, I enabled postgis because I need it for some, but not all, of my databases. Is it possible to have more than one template, and to specify which template to use when creating a new DB? Yes thats possible - createdb has an option -T to specify another template database. Regards Tino ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Simpler dump?
Douglas McNaught wrote: On 12/10/07, Ted Byers [EMAIL PROTECTED] wrote: Where will I find template1? When I look at the databases on the server, the only template I see is called template_postgis. Most of the extra stuff I see in all my databases relates to geometry that I find in this template. When I installed postgresql, I enabled postgis because I need it for some, but not all, of my databases. Is it possible to have more than one template, and to specify which template to use when creating a new DB? Yes, looking at the docs for CREATE DATABASE would probably be enlightnening for you. waiting for the topic called: simpler CREATE DATABASE coming up on this list ;-) SCNR ;) T. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] subversion support?
Hi Dave, Dave Page schrieb: --- Original Message --- From: Roberts, Jon [EMAIL PROTECTED] To: pgsql-general@postgresql.org Sent: 25/10/07, 17:35:32 Subject: Re: [GENERAL] subversion support? Complaint? Who is complaining? I am simply asking if this feature that is rather common in other database development tools will ever be added to pgAdmin. pgAdmin II had change control. No-one ever really used it though so we never bothered to implement it in pgAdmin III. But it was implemented differently then the proposal above. One way to implement it as easily as possible would be the ability to link editor windows to file on disk, where you could have the file version controled and changes to the file would show up immediately in the edit window where edits in the window could (with small delay) auto saved to the file. This way you need not change pgadmin much while you can use cvs/svn on your file system to do the VC stuff. only a clever way for mapping (maybe based on object type) configuration and the change detection (file notify, FAM, ...) (the latter depending on the OS unfortunately) Regards Tino ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] subversion support?
Hi, Roberts, Jon schrieb: I could use psql instead of pgAdmin then which isn't what I want. Having used Quest software SQL Navigator since 97 for Oracle and then migrated to Toad for Oracle which both products have integration to source control, it is hard to revert back to a command line or text file solution. Well you can still use gui tools and just let them work against a development database. With little scripting you can just dump the schema of that database periodically and check it in to SVN. Hook scripts can then take over the deployment (ideally based on tag creation) pgAdmin should graphically show differences between the committed version and the database. Does SQL Nav do this? At least the SQL Navigator/Toad support seems to heavily depend on server side code to help. This looks very unclean to the very least. It should allow me to click a button in the tool and commit it to the repository. It should allow me to revert back to a previous version and the tool take care of restoring the function automatically. You can test before you commit in the database - unlike Oracle, Postgres supports transactions even for DDL :-) (ok, I've yet find the button in pgadmin to disable auto commit :-) Regards Tino ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
