1 package DBIx::Class::Storage::DBI::Pg;
6 use base qw/DBIx::Class::Storage::DBI/;
9 use Context::Preserve 'preserve_context';
10 use DBIx::Class::Carp;
13 use DBIx::Class::Storage::DBI::Pg::Sth;
15 __PACKAGE__->sql_limit_dialect ('LimitOffset');
16 __PACKAGE__->sql_quote_char ('"');
17 __PACKAGE__->datetime_parser_type ('DateTime::Format::Pg');
18 __PACKAGE__->_use_multicolumn_in (1);
20 __PACKAGE__->mk_group_accessors('simple' =>
23 # these are package-vars to allow for evil global overrides
24 our $DEFAULT_USE_PG_CURSORS=0;
25 our $DEFAULT_PG_CURSORS_PAGE_SIZE=1000;
27 sub _determine_supports_insert_returning {
28 return shift->_server_info->{normalized_dbms_version} >= 8.002
34 sub with_deferred_fk_checks {
35 my ($self, $sub) = @_;
37 my $txn_scope_guard = $self->txn_scope_guard;
39 $self->_do_query('SET CONSTRAINTS ALL DEFERRED');
41 my $sg = Scope::Guard->new(sub {
42 $self->_do_query('SET CONSTRAINTS ALL IMMEDIATE');
45 return preserve_context { $sub->() } after => sub { $txn_scope_guard->commit };
48 # only used when INSERT ... RETURNING is disabled
50 my ($self,$source,@cols) = @_;
54 my $col_info = $source->columns_info(\@cols);
57 my $seq = ( $col_info->{$col}{sequence} ||= $self->dbh_do('_dbh_get_autoinc_seq', $source, $col) )
58 or $self->throw_exception( sprintf(
59 'could not determine sequence for column %s.%s, please consider adding a schema-qualified sequence to its column info',
64 push @values, $self->_dbh->last_insert_id(undef, undef, undef, undef, {sequence => $seq});
71 my ($self, $function, $sequence) = @_;
73 $self->throw_exception('No sequence to fetch') unless $sequence;
75 my ($val) = $self->_get_dbh->selectrow_array(
76 sprintf ("select %s('%s')", $function, (ref $sequence eq 'SCALAR') ? $$sequence : $sequence)
82 sub _dbh_get_autoinc_seq {
83 my ($self, $dbh, $source, $col) = @_;
86 my $table = $source->name;
88 # deref table name if it needs it
90 if ref $table eq 'SCALAR';
92 # parse out schema name if present
93 if( $table =~ /^(.+)\.(.+)$/ ) {
94 ( $schema, $table ) = ( $1, $2 );
97 # get the column default using a Postgres-specific pg_catalog query
98 my $seq_expr = $self->_dbh_get_column_default( $dbh, $schema, $table, $col );
100 # if no default value is set on the column, or if we can't parse the
101 # default value as a sequence, throw.
102 unless ( defined $seq_expr and $seq_expr =~ /^nextval\(+'([^']+)'::(?:text|regclass)\)/i ) {
103 $seq_expr = '' unless defined $seq_expr;
104 $schema = "$schema." if defined $schema && length $schema;
105 $self->throw_exception( sprintf (
106 'no sequence found for %s%s.%s, check the RDBMS table definition or explicitly set the '.
107 "'sequence' for this column in %s",
108 $schema ? "$schema." : '',
111 $source->source_name,
118 # custom method for fetching column default, since column_info has a
119 # bug with older versions of DBD::Pg
120 sub _dbh_get_column_default {
121 my ( $self, $dbh, $schema, $table, $col ) = @_;
123 # Build and execute a query into the pg_catalog to find the Pg
124 # expression for the default value for this column in this table.
125 # If the table name is schema-qualified, query using that specific
128 # Otherwise, find the table in the standard Postgres way, using the
129 # search path. This is done with the pg_catalog.pg_table_is_visible
130 # function, which returns true if a given table is 'visible',
131 # meaning the first table of that name to be found in the search
134 # I *think* we can be assured that this query will always find the
135 # correct column according to standard Postgres semantics.
139 my $sqlmaker = $self->sql_maker;
140 local $sqlmaker->{bindtype} = 'normal';
142 my ($where, @bind) = $sqlmaker->where ({
143 'a.attnum' => {'>', 0},
144 'c.relname' => $table,
146 -not_bool => 'a.attisdropped',
147 (defined $schema && length $schema)
148 ? ( 'n.nspname' => $schema )
149 : ( -bool => \'pg_catalog.pg_table_is_visible(c.oid)' )
152 my ($seq_expr) = $dbh->selectrow_array(<<EOS,undef,@bind);
155 (SELECT pg_catalog.pg_get_expr(d.adbin, d.adrelid)
156 FROM pg_catalog.pg_attrdef d
157 WHERE d.adrelid = a.attrelid AND d.adnum = a.attnum AND a.atthasdef)
158 FROM pg_catalog.pg_class c
159 LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
160 JOIN pg_catalog.pg_attribute a ON a.attrelid = c.oid
173 sub bind_attribute_by_data_type {
174 my ($self,$data_type) = @_;
176 if ($self->_is_binary_lob_type($data_type)) {
177 # this is a hot-ish codepath, use an escape flag to minimize
178 # amount of function/method calls
179 # additionally version.pm is cock, and memleaks on multiple
181 # the flag is stored in the DBD namespace, so that Class::Unload
182 # will work (unlikely, but still)
183 unless ($DBD::Pg::__DBIC_DBD_VERSION_CHECK_DONE__) {
184 if ($self->_server_info->{normalized_dbms_version} >= 9.0) {
185 try { DBD::Pg->VERSION('2.17.2'); 1 } or carp (
186 __PACKAGE__.': BYTEA columns are known to not work on Pg >= 9.0 with DBD::Pg < 2.17.2'
189 elsif (not try { DBD::Pg->VERSION('2.9.2'); 1 } ) { carp (
190 __PACKAGE__.': DBD::Pg 2.9.2 or greater is strongly recommended for BYTEA column support'
193 $DBD::Pg::__DBIC_DBD_VERSION_CHECK_DONE__ = 1;
196 return { pg_type => DBD::Pg::PG_BYTEA() };
203 sub _exec_svp_begin {
204 my ($self, $name) = @_;
206 $self->_dbh->pg_savepoint($name);
209 sub _exec_svp_release {
210 my ($self, $name) = @_;
212 $self->_dbh->pg_release($name);
215 sub _exec_svp_rollback {
216 my ($self, $name) = @_;
218 $self->_dbh->pg_rollback_to($name);
221 sub deployment_statements {
223 my ($schema, $type, $version, $dir, $sqltargs, @rest) = @_;
228 ! exists $sqltargs->{producer_args}{postgres_version}
230 my $dver = $self->_server_info->{normalized_dbms_version}
232 $sqltargs->{producer_args}{postgres_version} = $dver;
235 $self->next::method($schema, $type, $version, $dir, $sqltargs, @rest);
241 # cursors are per-connection, so reset the numbering
242 $self->_pg_cursor_number(1);
243 return $self->SUPER::_populate_dbh();
246 sub _get_next_pg_cursor_number {
249 my $ret=$self->_pg_cursor_number||0;
250 $self->_pg_cursor_number($ret+1);
255 sub __get_tweak_value {
256 my ($self,$attrs,$slot,$default,$extra_test)=@_;
258 $extra_test||=sub{1};
260 if ( exists $attrs->{$slot}
261 && defined $attrs->{$slot}
262 && $extra_test->($attrs->{$slot})
264 return $attrs->{$slot};
266 my @info=@{$self->_dbi_connect_info};
268 && ref($info[-1]) eq 'HASH'
269 && exists $info[-1]->{$slot}
270 && defined $info[-1]->{$slot}
271 && $extra_test->($info[-1]->{$slot})
273 return $info[-1]->{$slot};
278 sub _should_use_pg_cursors {
279 my ($self,$attrs) = @_;
281 return $self->__get_tweak_value($attrs,'use_pg_cursors',$DEFAULT_USE_PG_CURSORS);
284 sub _get_pg_cursor_page_size {
285 my ($self,$attrs) = @_;
287 return $self->__get_tweak_value($attrs,'pg_cursors_page_size',$DEFAULT_PG_CURSORS_PAGE_SIZE,
288 sub { $_[0] =~ /^\d+$/ });
293 my ($ident, $select, $where, $attrs) = @_;
295 # ugly ugly ugly, but this is the last sub in the call chain that receives $attrs
296 local $self->{_use_pg_cursors}=$self->_should_use_pg_cursors($attrs);
297 local $self->{_pg_cursor_page_size}=$self->_get_pg_cursor_page_size($attrs);
299 return $self->next::method(@_);
303 my ($self, $dbh, $sql) = @_;
305 if ($self->{_use_pg_cursors} && $sql =~ /^SELECT\b/i) {
306 return DBIx::Class::Storage::DBI::Pg::Sth
307 ->new($self,$dbh,$sql,$self->{_pg_cursor_page_size});
309 else { # short-circuit
310 return $self->next::method($dbh,$sql);
320 DBIx::Class::Storage::DBI::Pg - PostgreSQL-specific storage
324 Automatic primary key support:
326 # In your result (table) classes
327 use base 'DBIx::Class::Core';
328 __PACKAGE__->set_primary_key('id');
330 Using PostgreSQL cursors on fetches:
332 my $schema = MySchemaClass->connection(
336 pg_cursors_page_size => 1000,
339 # override at ResultSet level
340 my $rs = $schema->resultset('Something')
341 ->search({}, { use_pg_cursors => 0});
345 This class implements autoincrements for PostgreSQL.
347 It also implements fetching data via PostgreSQL cursors, as explained
348 in the documentation for L<DBD::Pg>.
350 =head1 CURSORS FETCHING SUPPORT
352 By default, PostgreSQL cursors are not used. You can turn them on (or
353 off again) either via the connection attributes, or via the ResultSet
354 attributes (the latter take precedence).
356 Fetching data using PostgreSQL cursors uses less memory, but is
357 slightly slower. You can tune the memory / speed trade-off using the
358 C<pg_cursors_page_size> attribute, which defines how many rows to
359 fetch at a time (defaults to 1000).
361 =head1 POSTGRESQL SCHEMA SUPPORT
363 This driver supports multiple PostgreSQL schemas, with one caveat: for
364 performance reasons, data about the search path, sequence names, and
365 so forth is queried as needed and CACHED for subsequent uses.
367 For this reason, once your schema is instantiated, you should not
368 change the PostgreSQL schema search path for that schema's database
369 connection. If you do, Bad Things may happen.
371 You should do any necessary manipulation of the search path BEFORE
372 instantiating your schema object, or as part of the on_connect_do
373 option to connect(), for example:
375 my $schema = My::Schema->connect
378 [ 'SET search_path TO myschema, foo, public' ],
384 See L<DBIx::Class/CONTRIBUTORS>
388 You may distribute this code under the same terms as Perl itself.