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' =>
25 sub _determine_supports_insert_returning {
26 return shift->_server_info->{normalized_dbms_version} >= 8.002
32 sub _determine_supports_server_cursors { 1 }
34 sub _use_server_cursors { 0 } # temporary global off switch
36 sub with_deferred_fk_checks {
37 my ($self, $sub) = @_;
39 my $txn_scope_guard = $self->txn_scope_guard;
41 $self->_do_query('SET CONSTRAINTS ALL DEFERRED');
43 my $sg = Scope::Guard->new(sub {
44 $self->_do_query('SET CONSTRAINTS ALL IMMEDIATE');
47 return preserve_context { $sub->() } after => sub { $txn_scope_guard->commit };
50 # only used when INSERT ... RETURNING is disabled
52 my ($self,$source,@cols) = @_;
56 my $col_info = $source->columns_info(\@cols);
59 my $seq = ( $col_info->{$col}{sequence} ||= $self->dbh_do('_dbh_get_autoinc_seq', $source, $col) )
60 or $self->throw_exception( sprintf(
61 'could not determine sequence for column %s.%s, please consider adding a schema-qualified sequence to its column info',
66 push @values, $self->_dbh->last_insert_id(undef, undef, undef, undef, {sequence => $seq});
73 my ($self, $function, $sequence) = @_;
75 $self->throw_exception('No sequence to fetch') unless $sequence;
77 my ($val) = $self->_get_dbh->selectrow_array(
78 sprintf ("select %s('%s')", $function, (ref $sequence eq 'SCALAR') ? $$sequence : $sequence)
84 sub _dbh_get_autoinc_seq {
85 my ($self, $dbh, $source, $col) = @_;
88 my $table = $source->name;
90 # deref table name if it needs it
92 if ref $table eq 'SCALAR';
94 # parse out schema name if present
95 if( $table =~ /^(.+)\.(.+)$/ ) {
96 ( $schema, $table ) = ( $1, $2 );
99 # get the column default using a Postgres-specific pg_catalog query
100 my $seq_expr = $self->_dbh_get_column_default( $dbh, $schema, $table, $col );
102 # if no default value is set on the column, or if we can't parse the
103 # default value as a sequence, throw.
104 unless ( defined $seq_expr and $seq_expr =~ /^nextval\(+'([^']+)'::(?:text|regclass)\)/i ) {
105 $seq_expr = '' unless defined $seq_expr;
106 $schema = "$schema." if defined $schema && length $schema;
107 $self->throw_exception( sprintf (
108 'no sequence found for %s%s.%s, check the RDBMS table definition or explicitly set the '.
109 "'sequence' for this column in %s",
110 $schema ? "$schema." : '',
113 $source->source_name,
120 # custom method for fetching column default, since column_info has a
121 # bug with older versions of DBD::Pg
122 sub _dbh_get_column_default {
123 my ( $self, $dbh, $schema, $table, $col ) = @_;
125 # Build and execute a query into the pg_catalog to find the Pg
126 # expression for the default value for this column in this table.
127 # If the table name is schema-qualified, query using that specific
130 # Otherwise, find the table in the standard Postgres way, using the
131 # search path. This is done with the pg_catalog.pg_table_is_visible
132 # function, which returns true if a given table is 'visible',
133 # meaning the first table of that name to be found in the search
136 # I *think* we can be assured that this query will always find the
137 # correct column according to standard Postgres semantics.
141 my $sqlmaker = $self->sql_maker;
142 local $sqlmaker->{bindtype} = 'normal';
144 my ($where, @bind) = $sqlmaker->where ({
145 'a.attnum' => {'>', 0},
146 'c.relname' => $table,
148 -not_bool => 'a.attisdropped',
149 (defined $schema && length $schema)
150 ? ( 'n.nspname' => $schema )
151 : ( -bool => \'pg_catalog.pg_table_is_visible(c.oid)' )
154 my ($seq_expr) = $dbh->selectrow_array(<<EOS,undef,@bind);
157 (SELECT pg_catalog.pg_get_expr(d.adbin, d.adrelid)
158 FROM pg_catalog.pg_attrdef d
159 WHERE d.adrelid = a.attrelid AND d.adnum = a.attnum AND a.atthasdef)
160 FROM pg_catalog.pg_class c
161 LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
162 JOIN pg_catalog.pg_attribute a ON a.attrelid = c.oid
175 sub bind_attribute_by_data_type {
176 my ($self,$data_type) = @_;
178 if ($self->_is_binary_lob_type($data_type)) {
179 # this is a hot-ish codepath, use an escape flag to minimize
180 # amount of function/method calls
181 # additionally version.pm is cock, and memleaks on multiple
183 # the flag is stored in the DBD namespace, so that Class::Unload
184 # will work (unlikely, but still)
185 unless ($DBD::Pg::__DBIC_DBD_VERSION_CHECK_DONE__) {
186 if ($self->_server_info->{normalized_dbms_version} >= 9.0) {
187 try { DBD::Pg->VERSION('2.17.2'); 1 } or carp (
188 __PACKAGE__.': BYTEA columns are known to not work on Pg >= 9.0 with DBD::Pg < 2.17.2'
191 elsif (not try { DBD::Pg->VERSION('2.9.2'); 1 } ) { carp (
192 __PACKAGE__.': DBD::Pg 2.9.2 or greater is strongly recommended for BYTEA column support'
195 $DBD::Pg::__DBIC_DBD_VERSION_CHECK_DONE__ = 1;
198 return { pg_type => DBD::Pg::PG_BYTEA() };
205 sub _exec_svp_begin {
206 my ($self, $name) = @_;
208 $self->_dbh->pg_savepoint($name);
211 sub _exec_svp_release {
212 my ($self, $name) = @_;
214 $self->_dbh->pg_release($name);
217 sub _exec_svp_rollback {
218 my ($self, $name) = @_;
220 $self->_dbh->pg_rollback_to($name);
223 sub deployment_statements {
225 my ($schema, $type, $version, $dir, $sqltargs, @rest) = @_;
230 ! exists $sqltargs->{producer_args}{postgres_version}
232 my $dver = $self->_server_info->{normalized_dbms_version}
234 $sqltargs->{producer_args}{postgres_version} = $dver;
237 $self->next::method($schema, $type, $version, $dir, $sqltargs, @rest);
243 # cursors are per-connection, so reset the numbering
244 $self->_pg_cursor_number(1);
245 return $self->SUPER::_populate_dbh();
248 sub _get_next_pg_cursor_number {
251 my $ret=$self->_pg_cursor_number||0;
252 $self->_pg_cursor_number($ret+1);
257 sub _should_use_pg_cursors {
258 my ($self,$attrs) = @_;
260 if ( exists $attrs->{server_cursors}
261 && defined $attrs->{server_cursors}
263 return $attrs->{server_cursors};
266 return $self->get_use_dbms_capability('server_cursors');
269 sub _get_pg_cursor_page_size {
270 my ($self,$attrs) = @_;
272 if ( exists $attrs->{cursor_page_size}
273 && defined $attrs->{cursor_page_size}
275 return $attrs->{cursor_page_size};
278 if (defined $self->cursor_page_size) {
279 return $self->cursor_page_size;
286 my ($ident, $select, $where, $attrs) = @_;
288 # ugly ugly ugly, but this is the last sub in the call chain that receives $attrs
289 local $self->{_use_pg_cursors}=$self->_should_use_pg_cursors($attrs);
290 local $self->{_pg_cursor_page_size}=$self->_get_pg_cursor_page_size($attrs);
292 return $self->next::method(@_);
296 my ($self, $dbh, $sql) = @_;
298 if ($self->{_use_pg_cursors} && $sql =~ /^SELECT\b/i) {
299 return DBIx::Class::Storage::DBI::Pg::Sth
300 ->new($self,$dbh,$sql,$self->{_pg_cursor_page_size});
302 else { # short-circuit
303 return $self->next::method($dbh,$sql);
313 DBIx::Class::Storage::DBI::Pg - PostgreSQL-specific storage
317 Automatic primary key support:
319 # In your result (table) classes
320 use base 'DBIx::Class::Core';
321 __PACKAGE__->set_primary_key('id');
323 Using PostgreSQL cursors on fetches:
325 my $schema = MySchemaClass->connection(
328 $schema->storage->set_use_dbms_capability('sever_cursors');
329 $schema->storage->cursor_page_size(1000);
331 # override at ResultSet level
332 my $rs = $schema->resultset('Something')
333 ->search({}, { server_cursors => 0});
337 This class implements autoincrements for PostgreSQL.
339 It also implements fetching data via PostgreSQL cursors, as explained
340 in the documentation for L<DBD::Pg>.
342 =head1 CURSORS FETCHING SUPPORT
344 By default, PostgreSQL cursors are not used. You can turn them on (or
345 off again) either via the connection attributes, or via the ResultSet
346 attributes (the latter take precedence).
348 Fetching data using PostgreSQL cursors uses less memory, but is
349 slightly slower. You can tune the memory / speed trade-off using the
350 C<cursor_page_size> attribute, which defines how many rows to
351 fetch at a time (defaults to 1000).
353 =head1 POSTGRESQL SCHEMA SUPPORT
355 This driver supports multiple PostgreSQL schemas, with one caveat: for
356 performance reasons, data about the search path, sequence names, and
357 so forth is queried as needed and CACHED for subsequent uses.
359 For this reason, once your schema is instantiated, you should not
360 change the PostgreSQL schema search path for that schema's database
361 connection. If you do, Bad Things may happen.
363 You should do any necessary manipulation of the search path BEFORE
364 instantiating your schema object, or as part of the on_connect_do
365 option to connect(), for example:
367 my $schema = My::Schema->connect
370 [ 'SET search_path TO myschema, foo, public' ],
376 See L<DBIx::Class/CONTRIBUTORS>
380 You may distribute this code under the same terms as Perl itself.