14,683 reputation
33471
bio website douglastechnology.co.uk
location Maidenhead, United Kingdom
age 39
visits member for 3 years
seen 13 hours ago

I'm an Oracle certified Database Administrator

I've worked extensively with both Oracle and Postgres, in various roles from DBA to developer. I have a basic working experience with SQL Server and MySQL.

I'm happy to consider offers of part-time or short-term work either in the London/M4 area or working remotely.

You can view my CV online


Mar
16
comment how to autoincrement varchar datatype depending upon the product details name using trigger
Are you aware that the select max(Slno) from product_details subquery will cause all your inserts to serialize? This is usually a terrible idea, which is why most RDBMSs provide some sort of auto_increment feature
Mar
16
comment Is this temp table behaviour documented?
Thanks for the suggestion Craig, I've raised it on pgsql-bugs (here).
Mar
15
revised restarting postgresql quickly
edited tags
Mar
15
asked Is this temp table behaviour documented?
Mar
14
reviewed Approve suggested edit on Contains statement Query
Mar
13
reviewed Approve suggested edit on Database tables to store all old relations between them (SQL)
Mar
13
comment Mirrored local accounts to connect to database on different server and domain
OP re-posted on SF here
Mar
13
comment What are the valid formats of a PostgreSQL schema name?
also try using the pgAdmin interface to create a table with a name that does not require quoting, eg tbluser instead of TBLUSER then check the generated SQL. Does it quote the table name? I think not. Speaking as an Oracle DBA, I see no reason why postgres should be more 'like' Oracle than any other RDBMS: if Oracle folk are finding the difference confusing that's no reason to upset the apple cart by changing the rules just for them: when in Rome...
Mar
13
comment What are the valid formats of a PostgreSQL schema name?
"Trying to access this table in a query with a non-quoted identifier will result in failure to locate the relation": that's because, as already mentioned in the comments, postgres folds unquoted identifiers to lowercase. Try create table "foo"(bar integer); select * from foo;.
Mar
11
comment How can streaming replication be kept lagged by a certain 'delay' time?
Thanks @Craig, very useful info
Mar
11
comment How can streaming replication be kept lagged by a certain 'delay' time?
That slave can then be taken out of the loop relatively quickly and we can take steps to reverse the damage without customers continuing to suffer
Mar
11
comment How can streaming replication be kept lagged by a certain 'delay' time?
It's a peculiar system (at least to my mind). A database is replicated from master to a number of slaves (currently 2) and these slaves are hit by customer queries. The master database receives updates daily which are not time-critical: over the long term they need to be applied but day-to-day it doesn't matter if customers see yesterdays data or even last weeks data. The idea behind the 'lag' is that if a daily update or any other change to the master passes QA but breaks things subtly, only one of the slaves will initially be affected (as the lag would be set to different periods on each)
Mar
10
comment How can streaming replication be kept lagged by a certain 'delay' time?
I was wondering about polling pg_last_xact_replay_timestamp() and pausing/resuming recovery. Possibly in conjunction with moving streamed WAL files when in a paused state.
Mar
10
comment How can streaming replication be kept lagged by a certain 'delay' time?
You mean you push the WAL rather than using streaming replication?
Mar
10
asked How can streaming replication be kept lagged by a certain 'delay' time?
Mar
8
revised Why would you index text_pattern_ops on a text column?
added bold in the quote and explanation about locales
Mar
7
comment Disk Latency vs Throughput
by "number of transactions" you mean the same thing as IOPS?
Mar
7
awarded  Nice Question
Mar
7
revised Disk Latency vs Throughput
added 131 characters in body
Mar
7
comment Disk Latency vs Throughput
to answer this properly we really need a bit more detail about how you are using the terms 'latency', 'throughput', 'faster' and even 'disk'. For example is the disk presented to the VM a real physical disk or a backing file on the host (or something else).