[GENERAL] why can't my account be used at wiki.postgresql.org after having registered in www.postgresql.org?
Hi, I just registered my acount using this url: https://www.postgresql.org/account/signup/, then i use the new account to login www.postgresql.org, everything works well. But when I try to login wiki.postgresql.org using the same account, i got an error: There is no user by the name myaccount. Check your spelling. Yet another page for registering at wiki.postgresql.org, named http://wiki.postgresql.org/wiki/Special:UserLogin/signup, tells me i can't signup a new account for wiki. how to register an acount for wiki.postgresql.org? Thanks! oebg
Re: [GENERAL] why can't my account be used at wiki.postgresql.org after having registered in www.postgresql.org?
On 14/12/2011 11:05, sunpeng wrote: Hi, I just registered my acount using this url: https://www.postgresql.org/account/signup/, then i use the new account to login www.postgresql.org http://www.postgresql.org, everything works well. But when I try to login wiki.postgresql.org http://wiki.postgresql.org using the same account, i got an error: There is no user by the name myaccount. Check your spelling. Yet another page for registering at wiki.postgresql.org http://wiki.postgresql.org, named http://wiki.postgresql.org/wiki/Special:UserLogin/signup, tells me i can't signup a new account for wiki. how to register an acount for wiki.postgresql.org http://wiki.postgresql.org? Thanks! oebg Hi there, Only guessing, but there was a major reconfiguration of the web infrastructure recently, which may be causing the problem. It would be a good idea to post to the pgsql-www mailing list also, as the web team monitors this one. Ray. -- Raymond O'Donnell :: Galway :: Ireland r...@iol.ie -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Philosophical question
Hi, I asked elsewhere about the best way to store db credentials within a user-session of a web-app. It appeared that it was for everybody but me evident that instead of heaving a db-role+passwd for every user of an application it was better to have just 1 set of db-credentials for the application and recreate a user management within the app instead using the existing user handling of the dbms. That way the app checks the user's password as a md5 in some table and remembers user is logged in for later. The actual queries would be done with a common set of real db credentials. Pro: Noone could bypass the app and use e.g. pgAdmin to access the DB instead of the app. Con: A bug in the app could give anyone the access level of the app's credentials which might offer admin rights if such power is needed at least for some users. What's your opinion? -- 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] Philosophical question
Hi, I asked elsewhere about the best way to store db credentials within a user-session of a web-app. It appeared that it was for everybody but me evident that instead of heaving a db-role+passwd for every user of an application it was better to have just 1 set of db-credentials for the application and recreate a user management within the app instead using the existing user handling of the dbms. That way the app checks the user's password as a md5 in some table and remembers user is logged in for later. The actual queries would be done with a common set of real db credentials. Pro: Noone could bypass the app and use e.g. pgAdmin to access the DB instead of the app. Con: A bug in the app could give anyone the access level of the app's credentials which might offer admin rights if such power is needed at least for some users. What's your opinion? Wel as usual 'it depends' One perspective is to create all users in your database and use a combination of views, functions and storedprocedures to handle actions and events. Basically this moves a great deal of management to the database instead of the app. Benefit would be that the Database offers a fixed interface for communication and you can completely redesign the backend without impacting the interface to the app. On the other end, when you use the app for those you often get a more finegrained control over functionality, since often (though not always) changing application functionality is easiser and you have the full power of PL/SQL at your direct disposal. This does integrate more, but makes you a lot less flexible imho. So it depends on where your (or your team's) skills are For the rest it depends on the criteria for the app. HTH Just my 2ct Kind regards/met vriendelijke groet, Serge Fonville http://www.sergefonville.nl Convince Google!! They need to add GAL support on Android (star to agree) http://code.google.com/p/android/issues/detail?id=4602 2011/12/14 Andreas maps...@gmx.net Hi, I asked elsewhere about the best way to store db credentials within a user-session of a web-app. It appeared that it was for everybody but me evident that instead of heaving a db-role+passwd for every user of an application it was better to have just 1 set of db-credentials for the application and recreate a user management within the app instead using the existing user handling of the dbms. That way the app checks the user's password as a md5 in some table and remembers user is logged in for later. The actual queries would be done with a common set of real db credentials. Pro: Noone could bypass the app and use e.g. pgAdmin to access the DB instead of the app. Con: A bug in the app could give anyone the access level of the app's credentials which might offer admin rights if such power is needed at least for some users. What's your opinion? -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/**mailpref/pgsql-generalhttp://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Philosophical question
On 14/12/2011 8:32 PM, Andreas wrote: Hi, I asked elsewhere about the best way to store db credentials within a user-session of a web-app. Where? Link? It appeared that it was for everybody but me evident that instead of heaving a db-role+passwd for every user of an application it was better to have just 1 set of db-credentials for the application and recreate a user management within the app instead using the existing user handling of the dbms. I usually prefer a hybrid, where the app logs in with a particular role with limited rights then does a SET ROLE to the app user it's currently operating as. Related to: http://stackoverflow.com/questions/8432636/in-postgresql-are-partitions-or-multiple-databases-more-efficient/8439618#8439618 -- Craig Ringer -- 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] Philosophical question
On Wed, Dec 14, 2011 at 4:32 AM, Andreas maps...@gmx.net wrote: Hi, I asked elsewhere about the best way to store db credentials within a user-session of a web-app. It appeared that it was for everybody but me evident that instead of heaving a db-role+passwd for every user of an application it was better to have just 1 set of db-credentials for the application and recreate a user management within the app instead using the existing user handling of the dbms. I prefer the db-role + password for a number of reasons including the ability to link into various auth options of PostgreSQL. Also it fits with my security strategy of drawing as narrow of a security perimeter as possible and thus making things more defensible. That way the app checks the user's password as a md5 in some table and remembers user is logged in for later. The actual queries would be done with a common set of real db credentials. Pro: Noone could bypass the app and use e.g. pgAdmin to access the DB instead of the app. On the other hand, you can prevent this using the pg_hba.conf, and if some user wants that permission, and has a legitimate reason for it, the permissions might be already set up to a reasonably safe level depending on what exactly is needed. Con: A bug in the app could give anyone the access level of the app's credentials which might offer admin rights if such power is needed at least for some users. Yes, the standard approach means the *application* has access to the the sum of all permissions needed. This is true on hybrid methods too, although hybrid methods have the benefit of not having all such access at once. I prefer to let PostgreSQL manage the user/passwords of my web apps. Best Wishes, Chris Travers -- 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] Postgresql connect into windows server
Hello, All ! But pg_admin on windows successfully works on both servers. Any ideas ? is a windows firewall blocking incoming connections on port 5432/tcp ? Possible yes, but which way I have to verify it ? When I add rule for 5432/tcp port, all works fine. Thanks a lot. -- Best regards, Sincerely yours, Yuriy Rusinov. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Copying timeline history file to standby
We are using 9.1., We have a set up like a master and 2 standby servers. M -- S1,S2 . Both standby S1 and S2 share the same archive. Master will have an Virtual IP. Both stand by servers will be replicated using this virtual ip. Assume the master fails,using our heart beat mechanism Virtual IP bound to S1(if S1 is ahead or equal to S2 XLOG)., Is it required to copy the time line history file that is generated at time of S1 promotion as master to the archive directory of S2 for replication to work (i.e S1(new master) to S2.) without restarting S2 or Is there any command in restore_command to be modified in recovery.conf -- Replication related configuration that are set in primary.,(postgresql.conf) wal_level = hot_standby hot_standby = on archive_mode = on archive_command = 'cp %p ../archive/%f' - recovery.conf: restore_command = 'cp ../archive/%f %p' standby_mode = 'on' primary_conninfo = 'host=IP port=5432 user=user password=pass' trigger_file = '/tmp/pg_failover_trigger' recovery_target_timeline='latest' --- *** if you copy(manually) the history file to the archive directory of S2 the replication catches up with S1 with out restarting S2. Without doing this history file copy from S1 to S2, S2 keeps throwing the following error message., 2011-12-07 17:29:46 IST::@:[18879]:FATAL: could not receive data from WAL stream: server closed the connection unexpectedly This probably means the server terminated abnormally before or while processing the request. cp: cannot stat `../archive/00010005': No such file or directory 2011-12-07 17:29:49 IST::@:[18875]:LOG: record with zero length at 0/5D8FFC0 cp: cannot stat `../archive/00010005': No such file or directory cp: cannot stat `../archive/0002.history': No such file or directory 2011-12-07 17:29:49 IST::@:[20362]:FATAL: timeline 2 of the primary does not match recovery target timeline 1 cp: cannot stat `../archive/00010005': No such file or directory cp: cannot stat `../archive/00010005': No such file or directory cp: cannot stat `../archive/0002.history': No such file or directory 2011-12-07 17:29:54 IST::@:[20367]:FATAL: timeline 2 of the primary does not match recovery target timeline 1 cp: cannot stat `../archive/00010005': No such file or directory cp: cannot stat `../archive/00010005': No such file or directory cp: cannot stat `../archive/0002.history': No such file or directory -- View this message in context: http://postgresql.1045698.n5.nabble.com/Copying-timeline-history-file-to-standby-tp5073536p5073536.html Sent from the PostgreSQL - general mailing list archive at Nabble.com. -- 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] why can't my account be used at wiki.postgresql.org after having registered in www.postgresql.org?
sunpeng blueva...@gmail.com writes: I just registered my acount using this url: https://www.postgresql.org/account/signup/, then i use the new account to login www.postgresql.org, everything works well. But when I try to login wiki.postgresql.org using the same account, i got an error: There is no user by the name myaccount. Check your spelling. You might try just waiting an hour or so --- I'm not sure how often those machines get synced. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Controlling complexity in queries
On Tue, Dec 13, 2011 at 4:27 PM, Jay Levitt jay.lev...@gmail.com wrote: Merlin Moncure wrote: Breaking your large queries into functions OTOH can make significant changes to the plan, often to the worse. As an end-user, I think this is an area where PostgreSQL could really stand out (that and the moon launch). In Rails-land, you don't have The DBA that writes queries. You have a developer and an ORM, and when they step outside that ORM to do the cool things SQL can do, all their DRY habits fall apart, because it's 1990 again and you can either write clear code or fast code but not both. As far as ORMs are concerned, I'll take 1990 over 2011 all day long. The heavy emphasis on procedural/OO coding tactics to model business applications IMNSHO is and always been a total catastrophe. The reasons for that are numerous: insane defect rates, brittle functional relationships, poor concurrency model etc. Enterprises move off SQL because they hate paying the dollars top SQL talent demands only to pay the ultimate price when development gets crushed under the weight of maintaining all that crappy code. This sad state of affairs has been encouraged by some of the top software vendors. But having to write one big query for performance feels exactly like having to write one big C function with unrolled loops. I'm currently taking a well-factored, function-based query and turning it INTO what Robert James is trying to get OUT of: a monolithic query. SQL has a very powerful abstraction feature: it's called a view. Good use of views is a key design feature for complex databases. Functions are generally not a good choice for query abstraction unless: *) you are working with scalars (string manipulation etc) *) you need non relational features like plpgsql exception handling/notice printing, etc *) this particular operation is not really optimizable anyways and you want to wrap it (WITH RECURSIVE for example) *) your function is inline-able (generally, a one liner sql function that is stable or immutable) etc In the end, the performance of your queries is going to be directly related to how well you map the problem into relational logic It's not just that, though; it's quite possible to think relationally and still fall down. There are plenty of cases where the human eye can see that a modular function can be inlined, but the optimizer can't. I have a pathological case: a query against a database with just a few thousand users takes 1.5 seconds on fast hardware, because it ends up scanning a cartesian product to get 16 rows, even before you get to the nested loops. In fact, most of the time the optimizer does a great job of inlining all my set-returning functions, once 9.0.6/9.1.2 rolled out. I've seen at least three equally ominous pieces that would have to happen to allow DRY, composable SQL: 1. Optional optimization of non-recursive WITH 2. Optional pushdown of WHERE clauses into GROUP BY[1] 3. LATERAL AFAIK, none of these are on anyone's short-term to-do list, and I'm sure none are easy. [1] Since this is my current favorite problem, the pathological case is: select questions.id from questions join ( select u.id from users as u group by u.id ) as s on s.id = questions.user_id where questions.id = 1; With users.id as a primary key, it's obvious that this can return only one row, but it has to scan the users table to get there. See the Subjquery in a JOIN not getting restricted? thread on pgsql-performance for Tom's explanation of why that's a hard problem to solve. Yeah -- here and there you run into difficult to optimize queries. (For my part, I'd just have converted that to WHERE EXISTS for the semi-join). merlin -- 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] Philosophical question
Andreas wrote: I asked elsewhere about the best way to store db credentials within a user-session of a web-app. It appeared that it was for everybody but me evident that instead of heaving a db-role+passwd for every user of an application it was better to have just 1 set of db-credentials for the application and recreate a user management within the app instead using the existing user handling of the dbms. That way the app checks the user's password as a md5 in some table and remembers user is logged in for later. The actual queries would be done with a common set of real db credentials. Pro: Noone could bypass the app and use e.g. pgAdmin to access the DB instead of the app. Con: A bug in the app could give anyone the access level of the app's credentials which might offer admin rights if such power is needed at least for some users. What's your opinion? You forgot the most important pro: If the web application server uses a single database user, you can use connection pooling, i.e. reuse connections instead of maintaining one connection per database user. This will boost performance. True, you could have a connection pool and use ALTER SESSION AUTHORIZATION to become a certain database user for one request, but that means that the application server login user must be a superuser, which is a terrible idea. Yours, Laurenz Albe -- 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] Philosophical question
Am 14.12.2011 14:28, schrieb Craig Ringer: On 14/12/2011 8:32 PM, Andreas wrote: Hi, I asked elsewhere about the best way to store db credentials within a user-session of a web-app. Where? Link? Well, it was on the general list of php.net. I read your link and understood your not a particular fan of PHP. I'm not exactly dogmatic about PHP either. It's just the first approach to the web-app topic for me. One has to start somewhere. :-} I'll need to let some specific external users access our PG DB that up until now uses a MS-Access frontend. PHP seemed to be the easiest approach without having to mess around with ASP or JAVA and all this. It appeared that it was for everybody but me evident that instead of heaving a db-role+passwd for every user of an application it was better to have just 1 set of db-credentials for the application and recreate a user management within the app instead using the existing user handling of the dbms. I usually prefer a hybrid, where the app logs in with a particular role with limited rights then does a SET ROLE to the app user it's currently operating as. Related to: http://stackoverflow.com/questions/8432636/in-postgresql-are-partitions-or-multiple-databases-more-efficient/8439618#8439618 I wasn't aware of the possibility to switch roles, yet. I'll explore this in more detail. Thanks -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Locking Tables Backup Inquiry
I'm wanted to find out why is it recommended or even an option to lock tables during a backup of a database? I've never experimented with database backups so I'm only guessing it locks / freezes the data so no changes can be made while the backup is in process, correct? Just curious and wasn't able to find an answer online. My next question is more complex but more of a recommendations. I'm looking to see how do you guys backup your databases? Do you simply cron 'pg_dump' command line or do you have a script that gets called in cron using 'pg_dump' / 'pg_dumpall'? Just looking for ideas / recommendations for a simple / quick way to back up 5 small databases on my server. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Cisco Systems fail
I've been using a network management tool for a number of years from cisco to manage storage networking (fibre channel). The thing is called Fabric Manager and I was thrilled that they supported pg for the backend when I first installed. However, their latest and greatest is frozen to pg 8.2. Sigh. I think they tripped over the datatypes not being automatically cast to TEXT. That's what spewed anyway when I tried it to go around them. Maybe there is porting opportunity for someone since they seem to have lost their way: http://www.cisco.com/en/US/docs/switches/datacenter/mds9000/sw/5_0/release/notes/fm/fm_rel_notes_507.html -- 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] Locking Tables Backup Inquiry
On 12/14/2011 11:52 AM, Carlos Mennens wrote: I'm wanted to find out why is it recommended or even an option to lock tables during a backup of a database? I've never experimented with database backups so I'm only guessing it locks / freezes the data so no changes can be made while the backup is in process, correct? Just curious and wasn't able to find an answer online. My next question is more complex but more of a recommendations. I'm looking to see how do you guys backup your databases? Do you simply cron 'pg_dump' command line or do you have a script that gets called in cron using 'pg_dump' / 'pg_dumpall'? Just looking for ideas / recommendations for a simple / quick way to back up 5 small databases on my server. I assume that is coming from a mysql world, where yes, locking is a good option. PG does not need it. Read up on MVCC. Your backup will select * from table and read it just fine. Other transactions can update/delete from the same table, but because of the magic of MVCC, the backup wont see em, and neither will be blocked. Meaning, your reads and writes dont block each other. Meaning, your app wont freeze while the backup runs. Yep, you simply cron a pg_dump. (dumpall if you want users/roles and all databases). No locking needed. -Andy -- 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] Locking Tables Backup Inquiry
On Wed, Dec 14, 2011 at 1:15 PM, Andy Colson a...@squeakycode.net wrote: Yep, you simply cron a pg_dump. (dumpall if you want users/roles and all databases). No locking needed. So how would one put this in cron if I wanted to run this everyday? 0 * * * * /usr/bin/pg_dumpall pg_dumpall.$DATE.sql Will that work above assuming I wanted to run this every day at that specific time? I'm just guessing since I've never created a Crontab or messed with PG backups. -- 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] Locking Tables Backup Inquiry
On 12/14/2011 12:26 PM, Carlos Mennens wrote: On Wed, Dec 14, 2011 at 1:15 PM, Andy Colsona...@squeakycode.net wrote: Yep, you simply cron a pg_dump. (dumpall if you want users/roles and all databases). No locking needed. So how would one put this in cron if I wanted to run this everyday? 0 * * * * /usr/bin/pg_dumpall pg_dumpall.$DATE.sql Will that work above assuming I wanted to run this every day at that specific time? I'm just guessing since I've never created a Crontab or messed with PG backups. google is your friend. this'll run every hour. 0 * * * * /usr/bin/pg_dumpall pg_dumpall.$DATE.sql try: 0 4 * * * /usr/bin/pg_dumpall pg_dumpall.$DATE.sql that'll run at 4am every day. Watch the path's, who know's what directory is current: 0 4 * * * /usr/bin/pg_dumpall /backup/pg_dumpall.$DATE.sql -Andy -- 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] Locking Tables Backup Inquiry
On Wed, Dec 14, 2011 at 1:38 PM, Andy Colson a...@squeakycode.net wrote: this'll run every hour. 0 * * * * /usr/bin/pg_dumpall pg_dumpall.$DATE.sql Thank you! try: 0 4 * * * /usr/bin/pg_dumpall pg_dumpall.$DATE.sql that'll run at 4am every day. When I run the command in my shell (not in Cron), I'm prompted for my login password. Should I change the permissions in pg_hba.conf and enable INHERIT grants on my user? Should I place this in who's Cron line? Postgres? Carlos? or Root? Watch the path's, who know's what directory is current: 0 4 * * * /usr/bin/pg_dumpall /backup/pg_dumpall.$DATE.sql Yes, I always check my paths and use full paths rather than symbolic links. -- 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] Locking Tables Backup Inquiry
On 12/14/2011 12:54 PM, Carlos Mennens wrote: On Wed, Dec 14, 2011 at 1:38 PM, Andy Colsona...@squeakycode.net wrote: this'll run every hour. 0 * * * * /usr/bin/pg_dumpallpg_dumpall.$DATE.sql Thank you! try: 0 4 * * * /usr/bin/pg_dumpallpg_dumpall.$DATE.sql that'll run at 4am every day. When I run the command in my shell (not in Cron), I'm prompted for my login password. Should I change the permissions in pg_hba.conf and enable INHERIT grants on my user? Should I place this in who's Cron line? Postgres? Carlos? or Root? Watch the path's, who know's what directory is current: 0 4 * * * /usr/bin/pg_dumpall /backup/pg_dumpall.$DATE.sql Yes, I always check my paths and use full paths rather than symbolic links. That's up to you I guess. Assuming the crontab is running as root, you could add a .pgpass to root's home, which should be secure enough. -Andy -- 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] why can't my account be used at wiki.postgresql.org after having registered in www.postgresql.org?
On Wed, Dec 14, 2011 at 16:13, Tom Lane t...@sss.pgh.pa.us wrote: sunpeng blueva...@gmail.com writes: I just registered my acount using this url: https://www.postgresql.org/account/signup/, then i use the new account to login www.postgresql.org, everything works well. But when I try to login wiki.postgresql.org using the same account, i got an error: There is no user by the name myaccount. Check your spelling. You might try just waiting an hour or so --- I'm not sure how often those machines get synced. There should be no waiting period for this, the wiki should work right away. What username did you use? I'll go check the logs for anything suspicious.. -- Magnus Hagander Me: http://www.hagander.net/ Work: http://www.redpill-linpro.com/ -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Vacuum and Large Objects
Hi I am having problems recovering storage from a Postgres 9.05 database that is used to hold large XML blocks for a week, before they are archived off line. The main tables are partitioned in daily partitions, and these are easy to manage, however the DB keeps growing despite using Vacuum (daily at 0700) and autovacuum (this does not seem to run, although the process is running). The system is insert only, and partitions are dropped when over 7 days of age. I believe the issue lies with pg_largeobject, it is split between 88 files of approx. 1G each. The Postgres settings are default, EXCEPT grep ^[a-z] postgresql.conf listen_addresses = '*' # what IP address(es) to listen on; port = 5432 # (change requires restart) max_connections = 1000 # (change requires restart) shared_buffers = 256MB # min 128kB work_mem = 4MB # min 64kB maintenance_work_mem = 256MB# min 1MB vacuum_cost_delay = 20ms# 0-100 milliseconds checkpoint_segments = 32# in logfile segments, min 1, 16MB each checkpoint_completion_target = 0.9 # checkpoint target duration, 0.0 - 1.0 checkpoint_warning = 60s# 0 disables archive_mode = off # allows archiving to be done constraint_exclusion = partition# on, off, or partition log_destination = 'stderr' # Valid values are combinations of logging_collector = on # Enable capturing of stderr and csvlog silent_mode = on# Run server silently. log_checkpoints = on log_line_prefix = '%t %d %u ' # special values: log_statement = 'none' # none, ddl, mod, all track_activities = on track_counts = on autovacuum = on # Enable autovacuum subprocess? 'on' log_autovacuum_min_duration = 250 # -1 disables, 0 logs all actions and autovacuum_max_workers = 3 # max number of autovacuum subprocesses autovacuum_naptime = 3min # time between autovacuum runs autovacuum_vacuum_threshold = 500 # min number of row updates before autovacuum_analyze_threshold = 100 # min number of row updates before autovacuum_vacuum_scale_factor = 0.1# fraction of table size before vacuum autovacuum_analyze_scale_factor = 0.05 # fraction of table size before analyze autovacuum_vacuum_cost_delay = 5ms # default vacuum cost delay for autovacuum_vacuum_cost_limit = 200 # default vacuum cost limit for statement_timeout = 0 # in milliseconds, 0 is disabled datestyle = 'iso, dmy' lc_messages = 'en_GB.UTF-8' # locale for system error message lc_monetary = 'en_GB.UTF-8' # locale for monetary formatting lc_numeric = 'en_GB.UTF-8' # locale for number formatting lc_time = 'en_GB.UTF-8' # locale for time formatting default_text_search_config = 'pg_catalog.english' Besides running VACUUM FULL pg_largeobject;, is there a way I can get autovacuum to start and clear this up? All the best Simon Simon Windsor Eml: mailto:simon.wind...@cornfield.org.uk simon.wind...@cornfield.org.uk Tel: 01454 617689 Mob: 07590 324560 There is nothing in the world that some man cannot make a little worse and sell a little cheaper, and he who considers price only is that man's lawful prey.
Re: [GENERAL] Controlling complexity in queries
Merlin Moncure wrote: SQL has a very powerful abstraction feature: it's called a view. Good use of views is a key design feature for complex databases. Functions are generally not a good choice for query abstraction unless: One more: * You want contextual queries. (I guess this is a special case of you need non relational features.) In my case, I want all queries against content to be filtered by their relevance to the current user. That can't go into a view, because views don't have parameters; I need a computed column that may be different every time I run the query, and depends on a piece of information (the current user ID) that Postgres can't know. Relevance itself is a weighed average of a bunch of factors which *also* need the user ID, like how similar are you to the user who authored that content? As far as I can tell, the only way to accomplish this is through pure-SQL functions, or by hand-crafting the ultimate SQL query that accomplishes those functions. It's much easier to work with select content.*, relevance(content.id, current_user.id) as r order by r than the underlying query. I'm not doing fancy OO stuff; I'm compartmentalizing the knowledge of what is relevance, what is similarity, and how do I fetch a collection of content. I'm not even in 1990. I'm in (quick Google) 1946, when subroutines were invented: We also wish to be able to arrange for the splitting up of operations into subsidiary operations. If views had parameters, I'd use views, but I suspect that a parameterized view would be very much like a pure-SQL set-returning function, ya? I'd love to find a better way to do this. Having just read Thinking In Sets, I am sure a *real* SQL programmer would create a view that cross joins every content row with every user, materialize it, and then restrict your eventual query with the user id. But reading that book always leaves me with the same two questions: Why is Joe Celko yelling at me? And what's SNOBOL? [1] Since this is my current favorite problem, the pathological case is: select questions.id from questions join ( select u.id from users as u group by u.id ) as s on s.id = questions.user_id where questions.id = 1; With users.id as a primary key, it's obvious that this can return only one row, but it has to scan the users table to get there. See the Subjquery in a JOIN not getting restricted? thread on pgsql-performance for Tom's explanation of why that's a hard problem to solve. Yeah -- here and there you run into difficult to optimize queries. (For my part, I'd just have converted that to WHERE EXISTS for the semi-join). I think I'm about to learn a very important relational-algebra equivalence... could you elaborate? Jay -- 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] when was pg_stat_reset() used in my server for the last time
On Wed, Dec 14, 2011 at 08:38, AI Rumman rumman...@gmail.com wrote: Is it possible to find out, when was pg_stat_reset() used in my server for the last time? I am using Postgresql 9.2 and I need to find out unused index. I assume you mean PostgreSQL 9.1.2 The pg_stat_database system view has a 'stats_reset' column, for each database. There's also the pg_stat_get_db_stat_reset_time() function. Regards, Marti -- 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] Philosophical question
On Thu, Dec 15, 2011 at 4:16 AM, Andreas maps...@gmx.net wrote: Well, it was on the general list of php.net. I read your link and understood your not a particular fan of PHP. I'm not exactly dogmatic about PHP either. It's just the first approach to the web-app topic for me. One has to start somewhere. :-} The biggest problem with PHP, imho, is actually that it's so easy to use. Anyone can get a WYSIWYG editor, save as HTML, and have a web page... and then all you need to do is rename it to .php and put some special tags in it, and look! You have a dynamic web page and it's so awesome! At least, it is until you try to go further, and you start adding mess on top of mess on top of mess. There are a few more fundamental issues with the language, but mainly, it gets the blame for myriad bad PHP programmers. I prefer Pike. It's designed for writing servers, performance is pretty decent, it's a high level language, and it has great database support (including Postgres-specific features, some of which are quite handy). Chris Angelico -- 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] Philosophical question
On Wed, 14 Dec 2011, Andreas wrote: Hi, I asked elsewhere about the best way to store db credentials within a user-session of a web-app. You might give this a read: http://database-programmer.blogspot.com/2009/02/comprehensive-database-security-model.html It goes through how using a DB user per app user would work. I don't necessarily agree with it, but it's good reading. -David -- 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] Controlling complexity in queries
[1] Since this is my current favorite problem, the pathological case is: select questions.id from questions join ( select u.id from users as u group by u.id ) as s on s.id = questions.user_id where questions.id = 1; With users.id as a primary key, it's obvious that this can return only one row, but it has to scan the users table to get there. See the Subjquery in a JOIN not getting restricted? thread on pgsql-performance for Tom's explanation of why that's a hard problem to solve. Yeah -- here and there you run into difficult to optimize queries. (For my part, I'd just have converted that to WHERE EXISTS for the semi-join). I think I'm about to learn a very important relational-algebra equivalence... could you elaborate? You could write that as: select questions.id from questions as q where exists (select 1 from users as u where u.id = q.user_id) and questions.id = 1; That's basically what you are doing, checking that a user with a given id from the questions table exists in the users table. Writing it as WHERE EXISTS is a matter of phrasing the question more accurately, which gives the query planner a hint that for your answer a single hit is sufficient - no need to check whether there are other matches after the first one. That said, wouldn't a foreign key constraint help you even better? If questions.user_id is required to refer to an existing users.id (by an FK constraint), than the check in the query becomes moot. Alban Hertroys -- Screwing up is an excellent way to attach something to the ceiling. -- 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] Philosophical question
Onsdag 14. desember 2011 22.21.04 skrev Chris Angelico : The biggest problem with PHP, imho, is actually that it's so easy to use. Anyone can get a WYSIWYG editor, save as HTML, and have a web page... and then all you need to do is rename it to .php and put some special tags in it, and look! You have a dynamic web page and it's so awesome! At least, it is until you try to go further, and you start adding mess on top of mess on top of mess. In my opinion, that's a pretty elitistic view. Certainly, that's one way of writing PHP, but it isn't the only one. Quite a few of us have started with something like what you've outlined here, but have long ago moved on to more maintainable coding practices. The good thing about PHP is the low threshold, and you can start using it doing exactly what you outlined in your first paragraph. But somebody coming to PHP from any old procedural language, will soon find that PHP lends itself well to building function upon function, until you can really write the code you need to express anything you want. There are a few more fundamental issues with the language, but mainly, it gets the blame for myriad bad PHP programmers. Yes there's a lot of bad programmers out there. Most of them code in Java or Visual Basic. I prefer Pike. It's designed for writing servers, performance is pretty decent, it's a high level language, and it has great database support (including Postgres-specific features, some of which are quite handy). I'd like to look at this Pike. I don't think that my Web host supports it, but it might still be a fun experience. regards, Leif The Yggdrasil project: http://code.google.com/p/yggdrasil-genealogy/ -- 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] Philosophical question
This is somewhat of a diversion but On Wed, Dec 14, 2011 at 4:25 PM, Leif Biberg Kristensen l...@solumslekt.org wrote: Onsdag 14. desember 2011 22.21.04 skrev Chris Angelico : The biggest problem with PHP, imho, is actually that it's so easy to use. Anyone can get a WYSIWYG editor, save as HTML, and have a web page... and then all you need to do is rename it to .php and put some special tags in it, and look! You have a dynamic web page and it's so awesome! At least, it is until you try to go further, and you start adding mess on top of mess on top of mess. In my opinion, that's a pretty elitistic view. Certainly, that's one way of writing PHP, but it isn't the only one. Quite a few of us have started with something like what you've outlined here, but have long ago moved on to more maintainable coding practices. The good thing about PHP is the low threshold, and you can start using it doing exactly what you outlined in your first paragraph. But somebody coming to PHP from any old procedural language, will soon find that PHP lends itself well to building function upon function, until you can really write the code you need to express anything you want. I take a different view. I actually think PHP is not a bad preprocessor for SGML documents. The fact that it pretty much sucks for everything else doesn't undermine that. This is one of the reasons why I think PL/PHP really should sit in a very tiny niche--- it could be very useful when combined with the xml data type, but I can't think of any other really defensible applications. If you look at PHP solely as an SGML preprocessor, it's just fine. However the further you get from this role, the more it breaks down. If there is a big criticism of PHP to be had, its that it breaks down too gracefully leading to programs written in the language which would be far better written in other languages. I can think of a few I have written which would have been far better off written in Perl or sed.. Hope this helps, Chris Travers -- 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] Vacuum and Large Objects
On 12/15/2011 04:01 AM, Simon Windsor wrote: Hi I am having problems recovering storage from a Postgres 9.05 database that is used to hold large XML blocks for a week, before they are archived off line. The main tables are partitioned in daily partitions, and these are easy to manage, however the DB keeps growing despite using Vacuum (daily at 0700) and autovacuum (this does not seem to run, although the process is running). The system is insert only, and partitions are dropped when over 7 days of age. I believe the issue lies with pg_largeobject, it is split between 88 files of approx. 1G each. Are you using pg_largeobject via the lo_ functions, or via the `lo' datatype? If you're using it via the `lo' type then certain actions can IIRC cause large object leaks. Try vacuumlo. http://www.postgresql.org/docs/current/static/vacuumlo.html vacuumlo is **NOT** suitable for use on databases where you use the lo_ functions directly. See also the `lo' module: http://www.postgresql.org/docs/current/static/lo.html If you're using the lo_ functions directly and still seeing excessive space consumption in pg_largeobject then beyond a CLUSTER or VACUUM FULL run I'm not sure what to advise. -- Craig Ringer
Re: [GENERAL] Philosophical question
On 12/15/2011 01:16 AM, Andreas wrote: Am 14.12.2011 14:28, schrieb Craig Ringer: On 14/12/2011 8:32 PM, Andreas wrote: Hi, I asked elsewhere about the best way to store db credentials within a user-session of a web-app. Where? Link? Well, it was on the general list of php.net. I read your link and understood your not a particular fan of PHP. I'm not, but all I was referring to there is that PHP's persistent connections are a very basic tool that for decent performance must be used with a connection pool. That can be either a pool built in to the database for those DBs that support it or via a proxy like PgPool or PgBouncer for DBs like Pg that don't. If that gave the impression I really dislike the language it wasn't intended. The runtime isn't as sophisticated, sure, but that has advantages as well as downsides. PHP is really handy for quickly putting together basic webapps and has a lot going for it, especially if you use the more recent versions, enable the security features, and ALWAYS USE PARAMETERISED STATEMENTS in SQL rather than string concatenation. I'll need to let some specific external users access our PG DB that up until now uses a MS-Access frontend. PHP seemed to be the easiest approach without having to mess around with ASP or JAVA and all this. Yep, it probably is. The only other thing I'd maybe want to play with is JBoss Seam Forge - however it's pretty immature and rather under-documented. -- Craig Ringer -- 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] Philosophical question
Em 14/12/2011 22:25, Leif Biberg Kristensen escreveu: Onsdag 14. desember 2011 22.21.04 skrev Chris Angelico : The biggest problem with PHP, imho, is actually that it's so easy to use. Anyone can get a WYSIWYG editor, save as HTML, and have a web page... and then all you need to do is rename it to .php and put some special tags in it, and look! You have a dynamic web page and it's so awesome! At least, it is until you try to go further, and you start adding mess on top of mess on top of mess. You can have the same with JSP (Java Server Pages). I really dislike it. You will be most by using OOP, a good design and a real, planned architecture. In my opinion, that's a pretty elitistic view. Certainly, that's one way of writing PHP, but it isn't the only one. Quite a few of us have started with something like what you've outlined here, but have long ago moved on to more maintainable coding practices. The good thing about PHP is the low threshold, and you can start using it doing exactly what you outlined in your first paragraph. But somebody coming to PHP from any old procedural language, will soon find that PHP lends itself well to building function upon function, until you can really write the code you need to express anything you want. There are a few more fundamental issues with the language, but mainly, it gets the blame for myriad bad PHP programmers. Yes there's a lot of bad programmers out there. Most of them code in Java or Visual Basic. Comments like these seems FUD for me. You can't judge people by the language of choice, but by the bad programming habits (IT: I do program in Java, as well in C, Cobol, Visual Basic, C#, few functional languages and some scripting languages). I can see that most bad programmers use VB or Java because these are the most used languages in the whole world... But I believe that there are more bad Javascript programmers (just check how many beauty-and-crap-never-work-as-planned-sites exists all around the world), only Javascript is not counted as a real programming language (who knows why?). You will find bad programmers on any language, including some languages that do not exists yet. Is the same if I say that bad DBA uses database X instead PostgreSQL. This is not true, because we can have bad DBA using PostgreSQL. FUD apart, I would add that by using the Java Platform (not the language - you can choose the language you want to use among hundreds JVM options), you can leverage authorization and authentication to well known and proven platform (and APIs) for user identification, that can easily rely on Database of choice, LDAP, Kerberos, NT-Auth, Linux auth among others (you can even extend and create a completely new one). Once the user has been authenticated, you can easly use JAAS API to get trusted user name (the one that has passed the authentication method of choice) to leverage your decision on how to connect to database (either by using generic database user, or by using set session authorization, etc). If you write your own provider, you can customize the code in order to have the database connection available directly by the custom JAAS provider. So you will have the best of any world you judge better for your needs. Regards, Edson. I prefer Pike. It's designed for writing servers, performance is pretty decent, it's a high level language, and it has great database support (including Postgres-specific features, some of which are quite handy). I'd like to look at this Pike. I don't think that my Web host supports it, but it might still be a fun experience. regards, Leif The Yggdrasil project: http://code.google.com/p/yggdrasil-genealogy/ -- 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] Controlling complexity in queries
Alban Hertroys wrote: select questions.id from questions join ( select u.id from users as u group by u.id ) as s on s.id = questions.user_id where questions.id = 1; You could write that as: select questions.id from questions as q where exists (select 1 from users as u where u.id = q.user_id) and questions.id = 1; That's basically what you are doing, checking that a user with a given id from the questions table exists in the users table. That said, wouldn't a foreign key constraint help you even better? If questions.user_id is required to refer to an existing users.id (by an FK constraint), than the check in the query becomes moot. Ahh, I see.. yes, this query is just the smallest possible query that exhibits the same not-using-the-index behavior as the real query, which needs columns from both questions and users, and thus needs the join. (And it has aggregates, and needs the GROUP BY too.) There already is a constraint, questions.user_id always refers to a real users.id, etc. This is actually a great case where relational thinking does NOT map well to functional composability; as Tom Lane pointed out, the solution is just add the WHERE clause to the subquery too. But the subquery is in a function that doesn't *know* it's being restricted, and (to me) shouldn't have to know; that's what the optimizer does for a living. FWIW, and this may help the OP, my plan for tackling the but I want readability AND performance issue is to 1. write a monolithic, optimized, incomprehensible version of the query 2. maintain the pretty functions alongside it 3. Write unit tests that confirm that the output of #1 and #2 is identical. Kinda like how gcc builds gcc and verifies that the output is the same as gcc building gcc building gcc. Jay -- 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] Philosophical question
On Thu, Dec 15, 2011 at 11:25 AM, Leif Biberg Kristensen l...@solumslekt.org wrote: Onsdag 14. desember 2011 22.21.04 skrev Chris Angelico : The biggest problem with PHP, imho, is actually that it's so easy to use. Anyone can get a WYSIWYG editor, save as HTML, and have a web page... and then all you need to do is rename it to .php and put some special tags in it, and look! You have a dynamic web page and it's so awesome! At least, it is until you try to go further, and you start adding mess on top of mess on top of mess. In my opinion, that's a pretty elitistic view. Certainly, that's one way of writing PHP, but it isn't the only one. Quite a few of us have started with something like what you've outlined here, but have long ago moved on to more maintainable coding practices. Yes, many people have started badly and grown from there. The trouble is that there are people - and, I think, quite a large number - who start badly and stay there because they're able to do what they want. And some of them are professional programmers, and that's one reason why I see a lot of bad PHP and Javascript code around. The good thing about PHP is the low threshold, and you can start using it doing exactly what you outlined in your first paragraph. But somebody coming to PHP from any old procedural language, will soon find that PHP lends itself well to building function upon function, until you can really write the code you need to express anything you want. Yes, it's good to have a low threshold. Yes, if you have programming expertise, you can express yourself in PHP. Doesn't change the fact that people who do NOT have programming expertise can get the idea that they can code, based on an example copied and pasted from the web and then modified a bit - and the fact that this happens in publishing environments, not in the safety of someone's private/personal experimentation. [Inserting a quote from Edson Richter rich...@simkorp.com.br] But I believe that there are more bad Javascript programmers (just check how many beauty-and-crap-never-work-as-planned-sites exists all around the world), only Javascript is not counted as a real programming language (who knows why?). This is the same principle at work. In my opinion, Javascript is a perfectly valid language (okay, apart from the naming mess - Javascript, JavaScript, ECMAScript, etc, etc, etc - fold all of them together into the notional entity that most people think of, and it's a fine language). It runs into the same problem as PHP does, with the additional low-ness of barrier to entry that you don't even need to have a web server that supports it. Good language, too many bad coders using it. [Back to Leif Biberg Kristensen l...@solumslekt.org] There are a few more fundamental issues with the language, but mainly, it gets the blame for myriad bad PHP programmers. Yes there's a lot of bad programmers out there. Most of them code in Java or Visual Basic. Yep, Java and VB get bad programmers too. So does C. My point is, though, that the _extremely_ low barrier to entry means that PHP gets a particular class of novice programmer and puts tools in their hands that some never bother to master, resulting in horrendous PHP code that's a nightmare to handle. I was stating that the biggest problem with PHP is bad PHP programmers, not bad language features. Although this above statement of mine is a little ambiguous; what I meant is that I do have some fundamental issues with the language (eg its scoping rules are a bit odd, and adorning variable names feels like syntactic salt, and it has acquired a lot of cruft eg register_globals), but that these issues are not really all that significant compared to the likelihood that a random piece of PHP code will be bad. I prefer Pike. It's designed for writing servers, performance is pretty decent, it's a high level language, and it has great database support (including Postgres-specific features, some of which are quite handy). I'd like to look at this Pike. I don't think that my Web host supports it, but it might still be a fun experience. It's extremely unlikely that a cheap web host will support it; Pike's designed for writing servers, not writing dynamic web pages. One of Pike's killer apps is a web server called Roxen, which (far as I know) will do everything that most people want Apache to do; but the language's primary derivation is from the world of MUDs. I have a server that's been running for a year and a half, and a Pike process that's been running on it ever since I fired up the server; it never needs to shut down, even to bring new code online. You can download a stable release from http://pike.ida.liu.se/ or get the latest source code via git. As a language, Pike has a lot going for it, but unfortunately the docs are somewhat lacking in places (particularly the GUI facilities - yes, a language designed for servers has GTK bindings). Chris Angelico -- Sent via pgsql-general
Re: [GENERAL] Philosophical question
A practice I like that I've seen done for a federal-government scale database program is to have each person using the application to login to the database using their own temporary database user. How it works is that the database has a users table similar to as if the application was managing users itself, and when the person logs in they are using credentials defined in that table. What actually happens for login is that there is a special database user which only has privileges to execute a single stored procedure, and the application's login screen talks to the database with that special user and invokes the procedure, giving the person-provided user and pass as procedure arguments. The stored procedure checks the database table, and if the credentials are accepted, the procedure then generates a new database user and password and gives these back to the application, which then turns around and logs in as the temporary user in order to do all the normal work of the person. This generated user only has the privileges that the person needs. This approach seems to have security benefits of some kinds. -- Darren Duncan -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Correct syntax to create partial index on a boolean column
For the boolean column Foo in Table1, if I want to index all values of TRUE, is this syntax correct? CREATE INDEX IDX_MyIndex ON Table1(Foo) WHERE Foo; The query: SELECT * FROM Table1 WHERE Foo; should use the index, and: SELECT * FROM Table1 WHERE NOT Foo; should not, correct? I just want to make sure I don't need an operator on the WHERE clause. Thanks! Mike -- 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] Philosophical question
Darren Duncan wrote: A practice I like that I've seen done for a federal-government scale database program is to have each person using the application to login to the database using their own temporary database user. How it works is that the database has a users table similar to as if the application was managing users itself, and when the person logs in they are using credentials defined in that table. What actually happens for login is that there is a special database user which only has privileges to execute a single stored procedure, and the application's login screen talks to the database with that special user and invokes the procedure, giving the person-provided user and pass as procedure arguments. The stored procedure checks the database table, and if the credentials are accepted, the procedure then generates a new database user and password and gives these back to the application, which then turns around and logs in as the temporary user in order to do all the normal work of the person. This generated user only has the privileges that the person needs. This approach seems to have security benefits of some kinds. -- Darren Duncan I should also clarify that this was a decade ago and the context was a desktop application which maintained an open connection for the person's work day. Although I think the same technique was also used there for web applications afterwards, that used the same database, if I don't mis-remember. I believe this was an Oracle 8 DBMS. -- Darren Duncan -- 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] Philosophical question
On Wed, Dec 14, 2011 at 8:14 PM, Chris Angelico ros...@gmail.com wrote: In my opinion, that's a pretty elitistic view. Certainly, that's one way of writing PHP, but it isn't the only one. Quite a few of us have started with something like what you've outlined here, but have long ago moved on to more maintainable coding practices. Yes, many people have started badly and grown from there. The trouble is that there are people - and, I think, quite a large number - who start badly and stay there because they're able to do what they want. And some of them are professional programmers, and that's one reason why I see a lot of bad PHP and Javascript code around. That's not a problem with PHP. I could name projects in Perl where a single developer started programming badly and 12 years later has barely improved. I argue the same for Python but haven't come across such projects yet. The good thing about PHP is the low threshold, and you can start using it doing exactly what you outlined in your first paragraph. But somebody coming to PHP from any old procedural language, will soon find that PHP lends itself well to building function upon function, until you can really write the code you need to express anything you want. Yes, it's good to have a low threshold. Yes, if you have programming expertise, you can express yourself in PHP. Doesn't change the fact that people who do NOT have programming expertise can get the idea that they can code, based on an example copied and pasted from the web and then modified a bit - and the fact that this happens in publishing environments, not in the safety of someone's private/personal experimentation. To be fair, isn't that an argument against whatever the popular programming language is of the day? I mean, we can all remember Matt's Scripting Archive and Perl. [Inserting a quote from Edson Richter rich...@simkorp.com.br] But I believe that there are more bad Javascript programmers (just check how many beauty-and-crap-never-work-as-planned-sites exists all around the world), only Javascript is not counted as a real programming language (who knows why?). This is the same principle at work. In my opinion, Javascript is a perfectly valid language (okay, apart from the naming mess - Javascript, JavaScript, ECMAScript, etc, etc, etc - fold all of them together into the notional entity that most people think of, and it's a fine language). It runs into the same problem as PHP does, with the additional low-ness of barrier to entry that you don't even need to have a web server that supports it. Good language, too many bad coders using it. Well, Javascript and PHP actually have two fundamentally different issues associated with them. PHP is lexically bound to the task of preprocessing SGML files, which is why all programming logic is typically contained within SGML PI tags. It's sort an upside-down templating system. When you combine this feature with the low barrier to entry things can get especially bad, specially as people start to try to do things like write GTK programs in the language (something it is most definitely not designed for). Additionally you have the fact that a lot of PHP documentation included examples which were subject to SQL injection exploits and the like.. Javascript is a nice light-weight automation language but until recently (and arguably even today!) it's use was largely limited to client-side scripting for web pages. Here you have a low barrier to entry combined with a niche where the barrier to entry probably should be reasonably low in the area of light-weight scripting, and so you run into problems when people start doing heavier-weight scripting. I the issues here are lower and the language is starting to spread out to other areas (NoSQL, node.js, and the like). There is a lot of bad Perl code out there too though. [Back to Leif Biberg Kristensen l...@solumslekt.org] There are a few more fundamental issues with the language, but mainly, it gets the blame for myriad bad PHP programmers. Yes there's a lot of bad programmers out there. Most of them code in Java or Visual Basic. Yep, Java and VB get bad programmers too. So does C. My point is, though, that the _extremely_ low barrier to entry means that PHP gets a particular class of novice programmer and puts tools in their hands that some never bother to master, resulting in horrendous PHP code that's a nightmare to handle. I was stating that the biggest problem with PHP is bad PHP programmers, not bad language features. Although this above statement of mine is a little ambiguous; what I meant is that I do have some fundamental issues with the language (eg its scoping rules are a bit odd, and adorning variable names feels like syntactic salt, and it has acquired a lot of cruft eg register_globals), but that these issues are not really all that significant compared to the likelihood that a random piece of PHP code
Re: [GENERAL] Philosophical question
On Thu, Dec 15, 2011 at 4:23 PM, Chris Travers chris.trav...@gmail.com wrote: So the problem is hardly limited to PHP. Oh, it definitely is not. Really, it's a problem with human beings who think they're programmers. It just seems to occur more frequently in some languages than others. ChrisA -- 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] Correct syntax to create partial index on a boolean column
For the boolean column Foo in Table1, if I want to index all values of TRUE, is this syntax correct? CREATE INDEX IDX_MyIndex ON Table1(Foo) WHERE Foo; The query: SELECT * FROM Table1 WHERE Foo; should use the index, and: SELECT * FROM Table1 WHERE NOT Foo; should not, correct? I just want to make sure I don't need an operator on the WHERE clause. Thanks! FYI, I've posted this on StackOverflow too in case anyone wants to score some points.. http://stackoverflow.com/questions/8514923/postgres-is-this-the-right-way-to-create-a-partial-index-on-a-boolean-column I'm 90% sure this is the right way to do it though. Mike -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] question about \encoding option of psql
Hi, I know \encoding is a meta command to set client encoding on psql prompt, but how can I set it inside the psql command line which will be called inside shell scripts, psql -h 192.168.72.7 -U gpadmin -w -d miner_demo -c\copy demo.store to 'd:\store.csv' with csv header How can I set the encoding for the export csv file? Regards, Xiaobo Gu -- 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] question about \encoding option of psql
On 12/14/11 10:12 PM, Xiaobo Gu wrote: I know \encoding is a meta command to set client encoding on psql prompt, but how can I set it inside the psql command line which will be called inside shell scripts, psql -h 192.168.72.7 -U gpadmin -w -d miner_demo -c\copy demo.store to 'd:\store.csv' with csv header How can I set the encoding for the export csv file? one thing that comes to mind, put the \encoding and \copy commands in a .sql file, and invoke it with psql -f file.sql -- john r pierceN 37, W 122 santa cruz ca mid-left coast -- 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] Correct syntax to create partial index on a boolean column
On Wed, Dec 14, 2011 at 9:54 PM, Mike Christensen m...@kitchenpc.com wrote: For the boolean column Foo in Table1, if I want to index all values of TRUE, is this syntax correct? CREATE INDEX IDX_MyIndex ON Table1(Foo) WHERE Foo; The query: SELECT * FROM Table1 WHERE Foo; should use the index, and: SELECT * FROM Table1 WHERE NOT Foo; should not, correct? I just want to make sure I don't need an operator on the WHERE clause. Thanks! FYI, I've posted this on StackOverflow too in case anyone wants to score some points.. http://stackoverflow.com/questions/8514923/postgres-is-this-the-right-way-to-create-a-partial-index-on-a-boolean-column I'm 90% sure this is the right way to do it though. Mike I've confirmed the index works as expected. I created 10,000 rows of random data, and set `diet_glutenfree` to `random() 0.9` so there's only a 10% chance of an `on` bit. I then re-created the indexes and tried the query again. SELECT RecipeId from RecipeMetadata where diet_glutenfree; Returns: 'Index Scan using idx_recipemetadata_glutenfree on recipemetadata (cost=0.00..135.15 rows=1030 width=16)' ' Index Cond: (diet_glutenfree = true)' And: SELECT RecipeId from RecipeMetadata where NOT diet_glutenfree; Returns: 'Seq Scan on recipemetadata (cost=0.00..214.26 rows=8996 width=16)' ' Filter: (NOT diet_glutenfree)' So, it will definitely use the index when I query for ON values. Just out of curiosity, is there a way to verify the number of rows that are indexed on a partial query? Mike -- 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] Correct syntax to create partial index on a boolean column
On 15 Dec 2011, at 5:43, Mike Christensen wrote: For the boolean column Foo in Table1, if I want to index all values of TRUE, is this syntax correct? CREATE INDEX IDX_MyIndex ON Table1(Foo) WHERE Foo; The query: SELECT * FROM Table1 WHERE Foo; should use the index, and: SELECT * FROM Table1 WHERE NOT Foo; should not, correct? Correct, but... That's not a particularly useful index to create. That index just contains values of true where the associated column equals true - you're storing the same information twice. It's generally more useful to index a column with values that you're likely to be interested in for limiting the result set further or for sorting or some-such, as long as the operation performed benefits from using an index. From your later example, for instance: SELECT RecipeId from RecipeMetadata where diet_glutenfree; If you plan to use this query in a join, an index like this would be more useful: CREATE INDEX recipemetadata_recipeid_glutenfree_idx ON RecipeMetadata(RecipeId) WHERE diet_glutenfree; That's a bit similar to creating an index on (RecipeId, diet_glutenfree), except that the latter also contains entries that are not gluten-free of course. Alban Hertroys -- The scale of a problem often equals the size of an ego. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
